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

3 Responses to WMI Alerts for Create Database and Create Login

  1. Pingback: Backing up Server Logins and Permissions « Mohsin's DBA Blog

  2. Tom Suykens says:

    Why making it complex, just create a server trigger. This allows you even to prevent creation.
    You can also create a backdoor

    example:
    CREATE trigger [ddl_CreateDatabase]
    on ALL Server
    for Create_database
    As
    If context_info() 0x4C6F6C20596F752064696420646F206974203A44 — backdoor
    begin
    RAISERROR (‘Creating databases has been disalowed’,10, 1)
    Rollback;
    end
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    ENABLE TRIGGER [ddl_CreateDatabase] ON ALL SERVER

    Note:
    If you need to implement this on multiple servers, I would advise to create a policy

    • dbamohsin says:

      Thanks for your Comment. Ive got to say ive not really played about with server triggers before. I’ll definitely look into this when I get a bit of free time!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: