Monitoring and Alerting for DDL Changes

Recently, I had a client that wanted to be alerted whenever a new database was created or dropped on a specific SQL instance. I thought, no problem, I will just create a trigger to fire off and use DBmail to send the alert. It was actually quite easy to get it built, but I must admit, it took me a few minutes to find out where I created the trigger.  Not everyone has created a server level DDL trigger; in fact, a lot of people have never even created a trigger before. So I thought maybe others might want to play with them too…

First step was to make sure DBmail was set up on my instance in question. You can follow the documentation on how to do that here. Once your DBmail is set up, it was as simple as writing a typical trigger with the exception of one small statement, ‘ON ALL SERVER’.

USE master
GO

CREATE TRIGGER [trig_DDL_Alert] ON ALL SERVER
FOR CREATE_DATABASE, DROP_DATABASE
AS
declare @DatabaseName VARCHAR(255)
declare @msgSubj varchar(max)
declare @msgTxt varchar(max)

SET @DatabaseName = (SELECT EVENTDATA().value(‘(/EVENT_INSTANCE/DatabaseName)[1]’, ‘VARCHAR(255)’))
SET @msgSubj = ‘DDL Update on ‘ + @@SERVERNAME + ‘ by ‘ + SUSER_SNAME()
SET @msgTxt =
(SELECT EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(max)’))
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘ @recipients = ”,
@body = @msgTxt,
@subject = @msgSubj
GO

ENABLE TRIGGER trig_DDL_Alert ON ALL SERVER
GO

It is important that you enable the trigger. Otherwise all you have done is created a trigger, but it will not fire off. Once you create and enable this trigger, you can try to create and drop a database to see what happens. You should get a few emails that look similar to the following.

email2Email1

Now, say you want to alter this trigger. You remember creating it in the Master DB, but you can’t seem to find it. A simple query will get you the info you need.

SELECT * FROM sys.server_triggers

You can also find all the server level triggers in SSMS by looking in the Server Objects > Triggers Folder on the Instance level.

There is plenty of DDL events that you can place triggers on. In case you wanted to monitor what your developers are creating on a sandbox server, you could set up triggers to alert in the creations for views, procedures and a lot of other items. You can see them all here.

Hopefully this helps you.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s