Setting up WMI server event alerts

In this recipe, we will set up a simple WMI server event alert for a DDL event.

Getting ready

We will set up an alert that creates a text file with timestamp every time there is a DDL login event (any of CREATE, ALTER, or DROP). We will utilize the WMI provider for server events in this exercise.

These are the values you will need to know:

Item	Value
Namespace (if using default instance)	root\Microsoft\SqlServer\ServerEvents\MSSQLServer
Namespace (if using named instance)	root\Microsoft\SqlServer\ServerEvents\InstanceName
WMI query	SELECT * FROM DDL_LOGIN_EVENTS
DDL_LOGIN_EVENTS Properties (partial list)	SQLInstance LoginName PostTime SPID ComputerName LoginType

For WMI events hitting SQL Server, you will also need to ensure that the SQL Server Broker is running on your target database. In our case, we need to ensure the broker is running on the msdb database. We can use the following snippet in T-SQL and run it in SQL Server Management Studio...