Monitor SQL Server Databases Changes Using WMI Alerts

Problem

We need to get notifications every time a database is created or deleted. Also, we would like to receive emails when there are any database level configuration changes. This will help us react to these changes immediately (for example, configure database backup for
a new database, configure transaction logs backups when the database recovery mode is changed, update maintenance plans and inventory when the database is deleted etc.).

Solution

In this tip you can find out how to setup email alerts using DDL triggers when new databases are created. In our tip we will explain how to setup universal notifications for databases modifications using WMI alerts.

SQL Server Agent Configuration

Before we configure alerts and create a job that will send notifications we
need to enable SQL Server Agent's "Replace tokens for all job responses to
alerts" option. To do this right click on SQL Server Agent in SSMS and
select Properties. Then select the Alert System page as shown below and
check the Token Replacement option and click OK to save the change.

This will allow SQL Server Agent to replace the tokens with running values (what database changed, who changed it etc.). Read more about using Tokens in Job Steps here (make sure you read
the security note in the article and understand the security risk).

Configure Database Mail

We also have to make sure that
Database Mail is configured for our SQL Server instance (note
the "Mail Profile" name that we will use later in our job):

Read this tip
about the Database Mail setup and configuration or this tip if you prefer to use the scripts for Database Mail configuration.

Create a SQL Server Agent Job

Now we are ready to setup a job that will respond to the alerts. The
job is named "WMI Response - DATABASE Class Event".

The image below displays the job's step. You will need to update @profile_name and @recipients parameters with your values (@profile_name will be the same as the "Mail profile" in the previous image):

Here is the complete script that you can use to create the job (update @profile and @recipients parameters to your values):

Make Sure Service Broker is Enabled

One of the requirements for the WMI Provider for Server Events to work is to make sure that Service Broker on msdb database is enabled. It is enabled by default, but you can verify it by running this query:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';

Testing Alerts

Now we should be able to receive email notifications when there are any changes to the databases.

First test, lets create a new database:

CREATE DATABASE [_Demo_DB1]
GO

You will get an email that will look similar to this:

Now we will update the database recovery model to FULL:

ALTER DATABASE [_Demo_DB1] SET RECOVERY FULL
GO

You will get an email with a different subject:

Note: This alert does not include database object creation or modification events, only database configuration changes monitored by this WMI event.

The last test, delete the database:

DROP DATABASE [_Demo_DB1]
GO

The email notification will look like this:

If you prefer to monitor only single events such as ("CREATE DATABASE" or "ALTER DATABASE" or "DROP DATABASE") you will need to setup your alerts
with different WMI queries like below:

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

DDL_DATABASE_EVENTS is not a table. It's Server Event Class that used by WMI. You don't need to create it and you can't directly query it with T-SQL. It exists as an event class and to use it you just need to create a WMI alert as described in the example above.

I have a link in my tip pointing to the Microsoft article explaining security risk and how to avoid it. Here is a quote from it:

"Any Windows user with write permissions on the Windows Event Log can access job steps that are activated by SQL Server Agent alerts or WMI alerts. To avoid this security risk, SQL Server Agent tokens that can be used in jobs activated by alerts are disabled by default. These tokens are: A-DBN, A-SVR, A-ERR, A-SEV, A-MSG., and WMI(property). Note that in this release, use of tokens is extended to all alerting.

If you need to use these tokens, first ensure that only members of trusted Windows security groups, such as the Administrators group, have write permissions on the Event Log of the computer where SQL Server resides. Then, right-click SQL Server Agent in Object Explorer, select Properties, and on the Alert System page, select Replace tokens for all job responses to alerts to enable these tokens."

So, if you configure access to the event log correctly/secure then this should not be a problem.

Yes, I have received an alert when I created the test SharePoint database using Central Management Administration ("Manage Content Databases"). I am surprised DDL triggers don't catch this. I haven't tested it, but I would think the triggers use the same event on background. Something to play with...

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.