Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to monitor DDL events in your DB

SQL 2005 DDL Triggers

How to monitor DDL events in your DB

by  ptheriault  Posted    (Edited  )
Beginning SQL Server 2005 we now have the ability to create triggers at the database and server level to monitor for DDL events. Here is a DDL trigger to monitor for Create, Alter and Drop table events in your databases.

First Create a log table. ( I put mine in the msdb database for this example but you can either create your own log db or place it in your user db)

Code: 
USE msdb
GO
CREATE TABLE ddl_log 
(ID int idenity(1,1) PRIMARY KEY CLUSTERED,
 PostTime datetime, 
 DB_User nvarchar(100), 
 Event nvarchar(100), 
 TSQL nvarchar(2000));
Here is the DDL Trigger. The trigger will add a record to the log table and send an e-mail with the DDL statement. If you want to ROLLBACK the DDL you can do so by adding the ROLLBACK statement before the insert.
Make sure you edit the email address.

Code: 
[color blue]Create[/color] [color blue]TRIGGER[/color] DDL_Notify 
[color blue]ON[/color] [color blue]DATABASE[/color] 
[color blue]FOR[/color] DROP_TABLE, ALTER_TABLE,CREATE_TABLE 
[color blue]AS[/color] 
[color blue]DECLARE[/color] @data XML,
        @tableHTML  NVARCHAR([color #FF00FF]MAX[/color]) ;

[color blue]SET[/color] @data = EVENTDATA()

[color blue]INSERT[/color] msdb.dbo.ddl_log (PostTime, DB_User, Event, TSQL) 
[color blue]VALUES[/color] ([color #FF00FF]GETDATE[/color](), [color #FF00FF]CONVERT[/color](nvarchar(100), [color #FF00FF]USER_NAME[/color]()), 
   @data.value([color red]'(/EVENT_INSTANCE/EventType)[1]'[/color], [color red]'nvarchar(100)'[/color]), 
   @data.value([color red]'(/EVENT_INSTANCE/TSQLCommand)[1]'[/color], [color red]'nvarchar(2000)'[/color]) ) ;

[color blue]SET[/color] @tableHTML =
    N[color red]'<H1>DDL Table Event</H1>'[/color] +
    N[color red]'<table border="1">'[/color] +
    N[color red]'<tr><th>Post Time</th><th>User</th>'[/color] +
    N[color red]'<th>TSQL</th><th></tr>'[/color] +
    [color #FF00FF]CAST[/color] ( ( [color blue]SELECT[/color] td = PostTime,       [color red]''[/color],
                    td = DB_User, [color red]''[/color],
                    td = TSQL, [color red]''[/color]
              [color blue]FROM[/color] msdb.dbo.ddl_log
              [color blue]WHERE[/color] id = ([color blue]select[/color] [color #FF00FF]max[/color](id) [color blue]from[/color] msdb.dbo.ddl_log)
              [color blue]FOR[/color] XML PATH([color red]'tr'[/color]), [color blue]TYPE[/color] 
    ) [color blue]AS[/color] NVARCHAR([color #FF00FF]MAX[/color]) ) +
    N[color red]'</table>'[/color] ;

[color blue]EXEC[/color] msdb.dbo.sp_send_dbmail 
    @profile_name = [color red]'Default'[/color],
    @recipients=[color red]'dba@youradress.com'[/color],
    @subject = [color red]'DDL Table Event'[/color],
    @body = @tableHTML,
    @body_format = [color red]'HTML'[/color] 

;

Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top