Create a DDL trigger on each database in the 2005 instance.

Question

Create a DDL trigger on each database in the 2005 instance.

I need to create a trigger on each database of my sql 2005 instance. I am setting up ddl triggers for auditing.

I am creating a cursor with all database names and trying to execute a USE statement. This doesn't seem to modify the database - the CREATE TRIGGER statement just fires multiple times in adventureworks. Another option would be to prefix the trigger object named databasename.dbo.triggername. This also does not work - some kind of limitation in creating triggers. Of course, I could do it manually, but I would prefer if it was written for easy application and removal. I have other options if I cannot do it in 1 sql script, but I would like to keep it simple :)

Here's what I have so far - hopefully you can find the bug in the bone!

--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 for about 100 or so databases. Hooray!

One caveat - your databases must be in compatibility mode 90 (in variants for each db), otherwise you might start getting errors. The account in EXECUTE AS included in the statement also needs insert access to 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'

+1

sql sql-server tsql sql-server-2005

Sam 02 dec. '08 at 16:54

source to share

6 answers

You don't need to create CURSOR ...

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

EDIT: "USE?" part is to switch to the specified database ... you might want to put an IF statement to make sure the database name is who you would like to be.

+1

Timothy Khouri 02 dec. '08 at 17:05

source to share

The first thing I would like to try is to put the "USE" command inside your @command line, although if it complains that the ddl trigger should be the first in the batch, it is unlikely to work.

Do you have access to Visual Studio? That would be pretty quick to code as a C # console application, leaving you with an exe file that you can run at any time. Not as transparent as sql script, but you can keep the source code along with it.

0

Clyde 02 dec. '08 at 17:39

source to share

Another possible solution would be to create a trigger on the model database so that all databases inherit it.

0

Jaime Febres 03 dec. '08 at 20:32

source to share

Thanks for this great post. I am just wandering, you can do a slightly easier approach this way:

CREATE TRIGGER trgMonitorNewDB ALL OVER THE SERVER FOR OBJECT_ACTION

see also link text

0

Yordan Georgiev Apr 10 09 at 5:50 am

source to share

This is a bit more brute force, but you can do a basic loop and print out the code you want to execute and then execute the code in a separate step. There is no need to unnecessarily jump over the hoops to use sp_msforeachdb ... assuming there is likely a limit on the number of addressable databases, which would make this approach impractical, but might help someone with a smaller set of databases:

`- First grab the list of database names, filter out whatever you don't want in the WHERE clause:

select name 
into #d 
from sys.databases

- Then, using your list at the top, skip and print the CREATE TRIGGER CODE for each database:

DECLARE @dbname varchar(100)
DECLARE @Trig VARCHAR(max)
select @dbname = (select top 1 name FROM #d order by name asc)

WHILE @dbname IS NOT NULL
BEGIN 
SET @Trig = 'USE ' + @dbname +'; 
GO

CREATE TRIGGER [DBA_KillerTrigger]
ON DATABASE
AFTER DDL_DATABASE_LEVEL_EVENTS
AS
/*...Trigger magic goes here...*/
GO

ENABLE TRIGGER [DBA_KillerTrigger] ON DATABASE
GO


PRINT @Trig

SELECT @dbname = (select top 1 name FROM #d where name > @dbname order by name asc)

END

0

DCaugs 10 Aug 15 at 19:27

source to share

Tom h · Accepted Answer · 2008-12-02T17:32:13+0000

When you use EXEC (), each use is used in its own context. So when you do EXEC ("USE MyDB"), it switches to MyDB for that context, after which the command ends, and you are back where you started. There are several possible solutions ...

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

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)

Another option is to do it as a two step process, where the first step generates and outputs the actual code with USE statements and all, then you run the generated code.

Create a DDL trigger on each database in the 2005 instance.

More articles: