StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCreate a DDL trigger in every database on a 2005 instance
text
Body
copied!<p>I need to create a trigger in every database on my sql 2005 instance. I'm setting up some auditing ddl triggers.</p> <p>I create a cursor with all database names and try to execute a USE statement. This doesn't seem to change the database - the CREATE TRIGGER statement just fires in adventureworks repeatedly. The other option would be to prefix the trigger object with databasename.dbo.triggername. This doesn't work either - some kind of limitation in creating triggers. Of course, I could do this manually, but I'd prefer to get it scripted for easy application and removal. I have other options if I can't do this in 1 sql script, but I'd like to keep it simple :)</p> <p>Here is what I have so far - hopefully you can find a bonehead mistake!</p> <pre><code>--setup stuff... CREATE DATABASE DBA_AUDIT GO USE DBA_AUDIT GO CREATE TABLE AuditLog (ID INT PRIMARY KEY IDENTITY(1,1), Command NVARCHAR(1000), PostTime DATETIME, HostName NVARCHAR(100), LoginName NVARCHAR(100) ) GO CREATE ROLE AUDITROLE GO sp_adduser 'guest','guest','AUDITROLE' GO GRANT INSERT ON SCHEMA::[dbo] TO AUDITROLE --CREATE TRIGGER IN ALL NON SYSTEM DATABASES DECLARE @dataname varchar(255), @dataname_header varchar(255), @command VARCHAR(MAX), @usecommand VARCHAR(100) SET @command = ''; --get the list of database names DECLARE datanames_cursor CURSOR FOR SELECT name FROM sys.databases WHERE name not in ('master', 'pubs', 'tempdb', 'model','msdb') OPEN datanames_cursor FETCH NEXT FROM datanames_cursor INTO @dataname WHILE (@@fetch_status = 0) BEGIN PRINT '----------BEGIN---------' PRINT 'DATANAME variable: ' + @dataname; EXEC ('USE ' + @dataname); PRINT 'CURRENT db: ' + db_name(); SELECT @command = 'CREATE TRIGGER DBA_Audit ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @data XML DECLARE @cmd NVARCHAR(1000) DECLARE @posttime NVARCHAR(24) DECLARE @spid NVARCHAR(6) DECLARE @loginname NVARCHAR(100) DECLARE @hostname NVARCHAR(100) SET @data = EVENTDATA() SET @cmd = @data.value(''(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'', ''NVARCHAR(1000)'') SET @cmd = LTRIM(RTRIM(REPLACE(@cmd,'''',''''))) SET @posttime = @data.value(''(/EVENT_INSTANCE/PostTime)[1]'', ''DATETIME'') SET @spid = @data.value(''(/EVENT_INSTANCE/SPID)[1]'', ''nvarchar(6)'') SET @loginname = @data.value(''(/EVENT_INSTANCE/LoginName)[1]'', ''NVARCHAR(100)'') SET @hostname = HOST_NAME() INSERT INTO [DBA_AUDIT].dbo.AuditLog(Command, PostTime,HostName,LoginName) VALUES(@cmd, @posttime, @hostname, @loginname);' EXEC (@command); FETCH NEXT FROM datanames_cursor INTO @dataname; PRINT '----------END---------' END CLOSE datanames_cursor DEALLOCATE datanames_cursor OUTPUT: ----------BEGIN--------- DATANAME variable: adventureworks CURRENT db: master Msg 2714, Level 16, State 2, Procedure DBA_Audit, Line 18 There is already an object named 'DBA_Audit' in the database. ----------END--------- ----------BEGIN--------- DATANAME variable: SQL_DBA CURRENT db: master Msg 2714, Level 16, State 2, Procedure DBA_Audit, Line 18 There is already an object named 'DBA_Audit' in the database. ----------END--------- </code></pre> <p>EDIT: I've already tried the sp_msforeachdb approach</p> <pre><code>Msg 111, Level 15, State 1, Line 1 'CREATE TRIGGER' must be the first statement in a query batch. </code></pre> <p>EDIT:</p> <p>Here is my final code - this exact script has not been tested, but it IS in production on about 100 or so databases. Cheers!</p> <p><strong>One caveat</strong> - your databases need to be in compatibility mode of 90(in options for each db), otherwise you may start getting errors. The account in the EXECUTE AS part of the statement also needs access to insert into your admin table.</p> <pre><code>USE [SQL_DBA] GO /****** Object: Table [dbo].[DDL_Login_Log] Script Date: 03/03/2009 17:28:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DDL_Login_Log]( [DDL_Id] [int] IDENTITY(1,1) NOT NULL, [PostTime] [datetime] NOT NULL, [DB_User] [nvarchar](100) NULL, [DBName] [nvarchar](100) NULL, [Event] [nvarchar](100) NULL, [TSQL] [nvarchar](2000) NULL, [Object] [nvarchar](1000) NULL, CONSTRAINT [PK_DDL_Login_Log] PRIMARY KEY CLUSTERED ( [DDL_Id] ASC, [PostTime] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --This creates the trigger on the model database so all new DBs get it USE [model] GO /****** Object: DdlTrigger [ddl_DB_User] Script Date: 03/03/2009 17:26:13 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [ddl_DB_User] ON DATABASE FOR DDL_DATABASE_SECURITY_EVENTS AS DECLARE @data XML declare @user nvarchar(100) SET @data = EVENTDATA() select @user = convert(nvarchar(100), SYSTEM_USER) execute as login='domain\sqlagent' INSERT sql_dba.dbo.DDL_Login_Log (PostTime, DB_User, DBName, Event, TSQL,Object) VALUES (@data.value('(/EVENT_INSTANCE/PostTime)[1]', 'nvarchar(100)'), @user, db_name(), @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'), @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(1000)') ) GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --CREATE TRIGGER IN ALL NON SYSTEM DATABASES DECLARE @dataname varchar(255), @dataname_header varchar(255), @command VARCHAR(MAX), @usecommand VARCHAR(100) SET @command = ''; DECLARE datanames_cursor CURSOR FOR SELECT name FROM sys.databases WHERE name not in ('master', 'pubs', 'tempdb', 'model','msdb') OPEN datanames_cursor FETCH NEXT FROM datanames_cursor INTO @dataname WHILE (@@fetch_status = 0) BEGIN PRINT '----------BEGIN---------' PRINT 'DATANAME variable: ' + @dataname; EXEC ('USE ' + @dataname); PRINT 'CURRENT db: ' + db_name(); SELECT @command = 'CREATE TRIGGER DBA_Audit ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @data XML DECLARE @cmd NVARCHAR(1000) DECLARE @posttime NVARCHAR(24) DECLARE @spid NVARCHAR(6) DECLARE @loginname NVARCHAR(100) DECLARE @hostname NVARCHAR(100) SET @data = EVENTDATA() SET @cmd = @data.value(''(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'', ''NVARCHAR(1000)'') SET @cmd = LTRIM(RTRIM(REPLACE(@cmd,'''',''''))) SET @posttime = @data.value(''(/EVENT_INSTANCE/PostTime)[1]'', ''DATETIME'') SET @spid = @data.value(''(/EVENT_INSTANCE/SPID)[1]'', ''nvarchar(6)'') SET @loginname = @data.value(''(/EVENT_INSTANCE/LoginName)[1]'', ''NVARCHAR(100)'') SET @hostname = HOST_NAME() INSERT INTO [DBA_AUDIT].dbo.AuditLog(Command, PostTime,HostName,LoginName) VALUES(@cmd, @posttime, @hostname, @loginname);' EXEC (@command); FETCH NEXT FROM datanames_cursor INTO @dataname; PRINT '----------END---------' END CLOSE datanames_cursor DEALLOCATE datanames_cursor -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----Disable all triggers when things go haywire sp_msforeachdb @command1='use [?]; IF EXISTS (SELECT * FROM sys.triggers WHERE name = N''ddl_DB_User'' AND parent_class=0)disable TRIGGER [ddl_DB_User] ON DATABASE' </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload