WMI Alerts for Create Database and Create Login

We had a need to know when a new database was created on a SQL Instance, and then further to find out whenever a login was created on a server.

My knowledge of WMI events is limited but i have managed to build a working alert for each of these conditions.

So, there are a couple of things to consider before WMI alerts will work:

    • Make sure service broker is enabled in msdb
-- Enable Service Broker: ALTER DATABASE [Database Name] SET ENABLE_BROKER; -- Disable Service Broker: ALTER DATABASE [Database Name] SET DISABLE_BROKER;

To check whether service broker is enabled

SELECT name, is_broker_enabled, service_broker_guid FROM sys.databases;

If service broker is enabled the result will be 1. If not it will be 0.More information about Service broker and why it is required for WMI events is explained here

  • Make sure the SQL Server Agent is started and make sure that “REPLACE TOKENS FOR ALL JOB RESPONSES TO ALERTS” option is checked under Agent properties -> Alert system

image

or by running the following T-SQL

USE [msdb]
GO EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1
GO

New Database Created

USE [msdb]
GO /****** Object: Alert [New Database Created] Script Date: 06/06/2011 11:15:23 ******/ EXEC msdb.dbo.sp_add_alert @name=N'New Database Created', @message_id=0, @severity=0, @enabled=1, @delay_between_responses=0, @include_event_description_in=1, @notification_message=N'Database $(ESCAPE_NONE(WMI(DatabaseName))) has been created on $(ESCAPE_NONE(WMI(ComputerName))) by $(ESCAPE_NONE(WMI(LoginName)))', @category_name=N'[Uncategorized]', @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', @wmi_query=N'Select * from CREATE_DATABASE' go EXEC msdb.dbo.sp_add_notification @alert_name=N'New Database Created', @operator_name=N'DBA Team', @notification_method = 1
GO

New Login Created

USE [msdb]
GO /****** Object: Alert [New Login Created] Script Date: 06/06/2011 11:35:37 ******/ EXEC msdb.dbo.sp_add_alert @name=N'New Login Created', @message_id=0, @severity=0, @enabled=1, @delay_between_responses=0, @include_event_description_in=1, @notification_message=N'Login $(ESCAPE_NONE(WMI(ObjectName))) has been created on $(ESCAPE_NONE(WMI(ComputerName))) by $(ESCAPE_NONE(WMI(LoginName)))', @category_name=N'[Uncategorized]', @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', @wmi_query=N'Select * from DDL_USER_EVENTS' EXEC msdb.dbo.sp_add_notification @alert_name=N'New Login Created', @operator_name=N'DBA Team', @notification_method = 1
GO

The examples above us the namespace for a default instance of SQL Server. To run for a names instance, the namespace should look like this:

@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\InstanceNAME'

For full details of all the Data Definition Language events on Event Groups see here

If the alerting is not working and you have followed the example above correctly, try a restart of the SQL Server Agent to kick start the alerting.

Advertisements