Monitor SQL Server Master Database Changes with WMI Alerts

By: Svetlana Golovko | Comments (2) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > Monitoring

Problem

Some of you probably had a situation where you check the SQL Server master database and find tables, stored procedures or users that are not supposed to be there.

Some of these objects might be created by running a script and not connecting to the right database (script executed on default database, no "USE database" statement in the script). Other objects could be created by a third party application's installation process (when application requires sysadmin's privileges during the installation time).

We do not always want to prevent these objects from being created as some of them potentially might be required, but we want to have notifications as soon as these objects are created. Someone who wants to compromise your server could create an object (for example a startup stored procedure) and then remove it. You may not know about this if you do not monitor these types of events. How can we monitor objects being created in the SQL Server master database?

Solution

There are a few options. We are not going to setup SQL Server Audit as it requires audit log reviews. We will setup WMI alerts for the real time notifications.

In one of my previous tips I explained how to setup WMI alerts for a database change monitoring. The setup consists of SQL Server Agent configuration steps, Database Mail configuration and creation of the alert and SQL Server Agent Job.

In this tip we will provide scripts for setting up WMI alerts and jobs to monitor the master database changes. The changes that we are going to monitor are objects creation/modification and users creation.

Make sure that Database Mail is configured and SQL Server Agent is setup to allow replacing tokens as per this tip.

SQL Server Agent job that will respond to the objects management events

The following job is going to respond to the WMI event every time when there is an object modification event in the master database.

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 "Mail profile" that you created during Database Mail configuration):

master DB object modification alert Job Step

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

EXEC msdb.dbo.sp_add_job 
	@job_name=N'WMI Response - Audit master Database Object Management Event', 
	@enabled=1, 
	@notify_level_eventlog=0, 
	@description=N'Sends email to DBA when master DB object modification event occur', 
	@owner_login_name=N'sa'

EXEC msdb.dbo.sp_add_jobstep 
	@job_name=N'WMI Response - Audit master Database Object Management Event', 
	@step_name=N'Send e-mail in response to WMI alert(s)', 
	@step_id=1, 
	@subsystem=N'TSQL', 
	@command=
	  N'DECLARE @p_body nvarchar(max)

	  SELECT @p_body = ''Server Name: $(ESCAPE_SQUOTE(WMI(ServerName)));
	  Start Time: $(ESCAPE_SQUOTE(WMI(STartTime))); 
	  Application Name: $(ESCAPE_SQUOTE(WMI(ApplicationName))); 
	  Host Name: $(ESCAPE_SQUOTE(WMI(HostName))); 
	  Login Name: $(ESCAPE_SQUOTE(WMI(LoginName)));
	  Session Login Name: $(ESCAPE_SQUOTE(WMI(SessionLoginName)));
	  ObjectName: $(ESCAPE_SQUOTE(WMI(ObjectName)));
	  EventSubClass: '' + CASE 
	  	WHEN $(ESCAPE_SQUOTE(WMI(EventSubClass))) =1  THEN ''Create'' 
		WHEN $(ESCAPE_SQUOTE(WMI(EventSubClass))) = 2  THEN ''Alter'' 
		WHEN $(ESCAPE_SQUOTE(WMI(EventSubClass))) = 3  THEN ''Drop'' 
		WHEN $(ESCAPE_SQUOTE(WMI(EventSubClass))) = 4  THEN ''Dump'' 
		WHEN $(ESCAPE_SQUOTE(WMI(EventSubClass))) = 10  THEN ''Open'' 
		WHEN $(ESCAPE_SQUOTE(WMI(EventSubClass))) = 11  THEN ''Load'' 
		WHEN $(ESCAPE_SQUOTE(WMI(EventSubClass))) = 12  THEN ''Access'' END
	  EXEC msdb.dbo.sp_send_dbmail
    	  @profile_name = ''DBServerAlerts'', -- update with your value
    	  @recipients = ''[email protected]'', -- update with your value
    	  @body = @p_body,
    	  @subject = ''master DB object change - $(ESCAPE_SQUOTE(WMI(ServerName)))'' ;', 
     	@database_name=N'msdb'
GO
EXEC msdb.dbo.sp_add_jobserver 
	@job_name=N'WMI Response - Audit master Database Object Management Event',  
	@server_name = @@SERVERNAME
GO

SQL Server Agent job that will respond to the user creation events

The following job is going to respond to the WMI event every time when a new user is created in the master database.

master DB user modification Alert Job Step

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

EXEC msdb.dbo.sp_add_job 
	@job_name=N'WMI Response - Audit Add master DB user Event', 
	@enabled=1, 
	@notify_level_eventlog=0, 
	@description=N'Sends email to DBA when user created in master DB', 
	@owner_login_name=N'sa'

EXEC msdb.dbo.sp_add_jobstep 
	@job_name=N'WMI Response - Audit Add master DB user Event', 
	@step_name=N'Send e-mail in response to WMI alert(s)', 
	@step_id=1, 
	@subsystem=N'TSQL', 
	@command=
		N'EXEC msdb.dbo.sp_send_dbmail
   		@profile_name = ''DBServerAlerts'', -- update with your value
  		@recipients = ''[email protected]'', -- update with your value
   		@body = ''Server Name: $(ESCAPE_SQUOTE(WMI(ServerName)));
			Start Time: $(ESCAPE_SQUOTE(WMI(STartTime))); 
			Application Name: $(ESCAPE_SQUOTE(WMI(ApplicationName))); 
			Host Name: $(ESCAPE_SQUOTE(WMI(HostName))); 
			Login Name: $(ESCAPE_SQUOTE(WMI(LoginName)));
			Session Login Name: $(ESCAPE_SQUOTE(WMI(SessionLoginName)));
			Target User Name: $(ESCAPE_SQUOTE(WMI(TargetUserName)));
			DBuserName: $(ESCAPE_SQUOTE(WMI(DBuserName)));'',
    		@subject = ''master DB user added - $(ESCAPE_SQUOTE(WMI(ServerName)))'' ;', 
	@database_name=N'msdb'
GO
EXEC msdb.dbo.sp_add_jobserver 
	@job_name=N'WMI Response - Audit Add master DB user Event',  
	@server_name = @@SERVERNAME
GO

Setting up WMI Alert to respond to the object creation events

Now we will setup the alerts.

First alert:

set the alert type to "WMI event alert"
make sure you use correct WMI namespace:

master DB object modification Alert setup

Note: the namespace will be different for the default instance vs. named instances:

-- DEFAULT instance's namespace ("DEMOSQL1" SQL Server):
\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER

-- NAMED instance's namespace ("DEMOSQL1\INSTANCE1" SQL Server):
\\.\root\Microsoft\SqlServer\ServerEvents\INSTANCE1

set the response in alert's properties to execute the job we created earlier:

master DB object modification Alert's response

Here is the script for the alert including alert's response:

EXEC msdb.dbo.sp_add_alert 
	@name=N'WMI - Audit master Schema Object Management Event', 
	@message_id=0, 
	@severity=0, 
	@enabled=1, 
	@delay_between_responses=10, 
	@include_event_description_in=1, 
	@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', 
	@wmi_query=N'select * from AUDIT_SCHEMA_OBJECT_MANAGEMENT_EVENT 
			where DatabaseName=''master''', 
	@job_name=N'WMI Response - Audit master Database Object Management Event'
GO

Setting up WMI Alert to respond to the users creation events

Second alert setup:

set the response in alert's properties to execute the job we created earlier:

master DB user modification Alert's response

Here is the script for the alert including alert's response:

EXEC msdb.dbo.sp_add_alert 
	@name=N'WMI - Audit Add master DB user Event', 
	@message_id=0, 
	@severity=0, 
	@enabled=1, 
	@delay_between_responses=10, 
	@include_event_description_in=1,  
	@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', 
	@wmi_query=N'select * from AUDIT_ADD_DB_USER_EVENT where DatabaseName=''master''', 
	@job_name=N'WMI Response - Audit Add master DB user Event'
GO

Testing the Alerts

Now we should be able to receive email notifications every time when somebody creates/modifies an object or creates a user in the master database.

Create a test user:

USE [master]
GO
CREATE LOGIN [_demo_user] 
	WITH PASSWORD=N'AlwaysStr0ngP@ssword', 
	DEFAULT_DATABASE=[master], 
	CHECK_EXPIRATION=ON, 
	CHECK_POLICY=ON
GO
CREATE USER [_demo_user] FOR LOGIN [_demo_user]
GO

You should get an email as follows:

Create a test object:

CREATE PROC dbo._Demo_masterProc_1
AS 
SELECT 1
GO

You will get a following email:

Modify the same object as a different user:

SETUSER '_demo_user'
GO
ALTER PROC dbo._Demo_masterProc_1
AS 
SELECT 2
GO

You should get an email with different "Login Name" and "Session Login Name":

Setting up these alerts will help you to monitor unauthorized objects or users created in the master database.

Note: Do not forget to disable the objects modification alert before SQL Server Service Packs and Cumulative Updates installation.

Next Steps

Run regular checks on your master databases to make sure there are no unexpected objects/users.
Refer to the previous tip #1, tip #2 and tip #3 about setting up other WMI alerts.
Read this tip about How to Automate SQL Server Monitoring with Email Alerts.
Read this tip about How to setup SQL Server alerts and email operator notifications.
Read these tips about Audit and Compliance.
Get familiar with "WMI Provider for Server Events Concepts".
Use other classes for your SQL Server events monitoring.
Get a list of columns that are available for a given WMI event XML schema.

About the author

Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Wednesday, May 6, 2015 - 11:09:27 PM - Sharon Rimer	Back To Top (37113)
The @wmi_query could not be executed in the @wmi_namespace provided. Verify that an event class selected in the query exists in the namespace and that the query has the correct syntax.