Use DDL Triggers to get Instance and Database Change Notifications

I have been working on some improvements to some of the regular ways we monitor for important changes.  We always have to be on the lookout for unexpected changes being made in the SQL instances that we monitor since often times we are not the only team who has sysadmin access to the instance.  We are always the best trained to take care of and configure things but we sometimes find that someone makes a change either to the SQL or database configuration without telling us.  We want to know when things like this happen!

Two of those important changes that I want to know about are when configuration changes are made to a SQL instance and when something about a database is changed.  The database change can be adding or removing files, new or dropped databases, changes in state or configuration changes.  It’s easy to get immediate notification of all of these changes by putting in a few DDL triggers at the server level.  I will show you how to do this on my local test instance.

Standard Disclaimer –– Please make sure to initially test these DDL triggers in a development or test server and only move to production when you are sure that things work as expected. — Standard Disclaimer

Requirements:  To make this work you need to have a SQL instance with Database Mail configured and able to send email.  Substitute your mail profile name everywhere you see “DBMail” and substitute your email where you see somebody@somecompany.com.

Instance Configuration Change Notification

Use the following to create the DDL trigger that will notify you when something is changed in the instance configuration:

USE master
GO
-- Trigger to notify of instance configuration changes
CREATE TRIGGER [ddl_trig_alter_inst_config]
ON ALL SERVER
FOR ALTER_INSTANCE
AS
DECLARE @DDLText VARCHAR(MAX)
DECLARE @EmailSubject VARCHAR(MAX)
DECLARE @CharInxOutput INT
SET @EmailSubject = 'Instance configuration changed on ' + @@SERVERNAME + ' by ' + SUSER_SNAME() 
SET @DDLText = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(MAX)'))
SET @CharInxOutput = CHARINDEX('show', @DDLText)

IF @CharInxOutput = 0  -- Use this to filter out all the 'show advanced options' statements that occur when using the GUI
	EXEC msdb.dbo.sp_send_dbmail
	 @profile_name = 'DBMail',
	 @recipients = 'somebody@somecompany.com',
	 @body = @DDLText,
	 @subject = @EmailSubject
GO

Now make a configuration change:

EXEC sys.sp_configure N'max server memory (MB)', N'7000'  -- Changes max memory to 7GB
GO
RECONFIGURE WITH OVERRIDE
GO

And watch your email:

Database Configuration Change Notifications

Use the following to create DDL triggers that will notify you when something is changed in any database in the instance:

-- Trigger to notify of new database created
CREATE TRIGGER [ddl_trig_new_db]
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE @DDLText VARCHAR(MAX)
DECLARE @EmailSubject VARCHAR(MAX)
SET @EmailSubject = 'Database Created on ' + @@SERVERNAME + ' by ' + SUSER_SNAME() 
SET @DDLText = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(MAX)'))

EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'DBMail',
 @recipients = 'somebody@somecompany.com',
 @body = @DDLText,
 @subject = @EmailSubject
GO

-- Trigger to notify of dropped database
CREATE TRIGGER [ddl_trig_drop_db]
ON ALL SERVER
FOR DROP_DATABASE
AS
DECLARE @DDLText VARCHAR(MAX)
DECLARE @EmailSubject VARCHAR(MAX)
SET @EmailSubject = 'Database dropped on ' + @@SERVERNAME + ' by ' + SUSER_SNAME() 
SET @DDLText = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(MAX)'))

EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'DBMail',
 @recipients = 'somebody@somecompany.com',
 @body = @DDLText,
 @subject = @EmailSubject
GO

-- Trigger to notify of changed database
CREATE TRIGGER [ddl_trig_alter_db]
ON ALL SERVER
FOR ALTER_DATABASE
AS
DECLARE @DDLText VARCHAR(MAX)
DECLARE @EmailSubject VARCHAR(MAX)
SET @EmailSubject = 'Database altered on ' + @@SERVERNAME + ' by ' + SUSER_SNAME() 
SET @DDLText = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(MAX)'))

EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'DBMail',
 @recipients = 'somebody@somecompany.com',
 @body = @DDLText,
 @subject = @EmailSubject
GO

-- Trigger to notify of database scoped changes
CREATE TRIGGER [ddl_trig_alter_db_scoped]
ON ALL SERVER
FOR ALTER_DATABASE_SCOPED_CONFIGURATION
AS
DECLARE @Qry VARCHAR(MAX)
DECLARE @EmailSubject VARCHAR(MAX)
DECLARE @DBName VARCHAR(255)
SET @DBName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)'))
SET @EmailSubject = @DBName + ' database altered on ' +  @@SERVERNAME + ' by ' + SUSER_SNAME() 
SET @Qry = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(MAX)'))

EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'DBMail',
 @recipients = 'somebody@somecompany.com',
 @body = @Qry,
 @subject = @EmailSubject
GO

I tested a bunch of different database changes including taking one offline, and all different kinds of configuration changes but may not have run into the specific change that cannot be captured using these methods.  Here are some of the notifications that I received:

I do have one thing that you need to be aware of when a new database is created with the DDL triggers in place.  Not only will you receive the above notice, you will receive a notification of all of the database configuration that is set up when it is created.  So you need to be ready receive a bunch of emails when a new database is created.  I received 28 emails of all the configuration settings in mine, so just be ready.  However, I feel like it is better to know about new databases being created in production environments than to find out about it later on.

If you want to see what DDL triggers might be present on your system or just want to check out the triggers we just made, all you have to do is query sys.server_triggers.

SELECT * FROM sys.server_triggers

Cleanup!  Cleanup!  Everybody cleanup!!

Here is how to get rid of the triggers when you are done testing.  I always try to provide cleanup statements since I am an OCD clean freak on my SQL Servers.

--Cleanup
DROP TRIGGER ddl_trig_alter_inst_config  
ON ALL SERVER;  
GO

DROP TRIGGER ddl_trig_new_db  
ON ALL SERVER;  
GO
DROP TRIGGER ddl_trig_drop_db  
ON ALL SERVER;  
GO
DROP TRIGGER ddl_trig_alter_db  
ON ALL SERVER;  
GO
DROP TRIGGER ddl_trig_alter_db_scoped  
ON ALL SERVER;  
GO

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!