Create a DDL trigger in every database on a 2005 instance

Question

I need to create a trigger in every database on my sql 2005 instance. I'm setting up some auditing ddl triggers.

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 :)

Here is what I have so far - hopefully you can find a bonehead mistake!

--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---------

EDIT: I've already tried the sp_msforeachdb approach

Msg 111, Level 15, State 1, Line 1
'CREATE TRIGGER' must be the first statement in a query batch.

EDIT:

Here is my final code - this exact script has not been tested, but it IS in production on about 100 or so databases. Cheers!

One caveat - 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.

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'

Answer 1

You don't have to create a CURSOR...

sp_msforeachdb 'USE ?; PRINT ''Hello ?'''

EDIT: The "USE ?" part is to switch to the specified database... you may want to put an IF statement to make sure that the database name is what you'd like it to be.

Answer 2

When you use EXEC() each use is in its own context. So, when you do EXEC('USE MyDB') it switches to MyDB for that context then the command ends and you're back where you started. There are a couple of possible solutions...

You can call sp_executesql with a database name (for example, MyDB..sp_executesql) and it will run in that database. The trick is to let you do that dynamically, so you basically wrap it twice like so:

DECLARE @cmd NVARCHAR(2000), @my_db VARCHAR(255)

SET @my_db = 'MyDatabaseName'

SET @cmd = 'DECLARE @my_cmd NVARCHAR(2000); SET @my_cmd = ''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 ' + @my_db + '..sp_executesql @my_cmd'

EXEC (@cmd)

The other option is to do this as a two-step process where the first step generates and prints out the actual code with USE statements and all, then you run that generated code.

Answer 3

The first thing I would try is to put the 'USE ' command inside your @command string although if it's complaining that the trigger ddl must be first in the batch, that's unlikely to work.

Do you have access to Visual Studio? This would be fairly quick to code in like a C# console app, leaving you an exe file you can run at any time. Not quite as transparent as a sql script but you can store the source code along side it.

Answer 4

Another possible solution could be to create the trigger in the model database, in this way all the databases will inherit it.

Answer 5

Hi, Thanks for this great post. I just wander could you take a slightly easier approach in this way :

CREATE TRIGGER trgMonitorNewDB ON ALL SERVER FOR OBJECT_ACTION

see alsolink text