Use DDL Triggers to get Instance and Database Change Notifications

By Lori Brown | Helpful Scripts

May 31

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:

Now make a configuration change:

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:

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.

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.

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!

  • […] Lori Brown shows how you can use DDL triggers to track database or instance-level changes: […]

  • >