Automated WMI Alerts for SQL Server Login Property Changes

By: Svetlana Golovko | Updated: 2018-06-19 | Comments (1) | Related: > Security

Problem

As you probably know, you can monitor login or user modifications using SQL Server Audit. The disadvantage of this method is that it requires reviewing the audit logs. We would like to get real-time alerts every time a login's properties change. In our previous WMI tip, we provided steps to configure alerts for login and user creation. Can we use WMI Alerts to monitor login property changes?

Solution

In one of our previous tips, we 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 Job. You will need to make sure Database Mail is configured and SQL Server Agent is setup to allow replacing tokens as per this tip.

In this tip we will show you how to set up WMI alerts and jobs responding to these alerts to monitor the following changes:

SQL Server login was enabled/disabled
SQL Server login was granted/denied SQL Server Access

SQL Server login's password policy and/or expiration was changed
SQL Server login's credential was changed
SQL Server password was reset/changed

Here are the jobs and alerts that will be created:

We will provide the jobs' steps and alerts screenshots in this tip and a complete script at the end of the tip for all of the jobs and alerts. In this tip we will extend the WMI alerts functionality and include an indication of a successful vs. failed change. See the details below under the first alert’s response job.

Note, that the jobs are not scheduled and cannot be run manually.

Create SQL Server Agent Job that will Send an Alert when a Login is Enabled/Disabled

The following job ("WMI Response - Audit Enable/Disable Login Event") will respond to the WMI event every time a login is enabled or disabled.

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):

"WMI Response - Audit Enable/Disable Login Event" job step

Note, that we have a new parameter @p_importance. We will use this parameter to set an email priority. The priority will be set to "High" when an action (login property change) failed:

@p_importance = CASE WHEN $(ESCAPE_SQUOTE(WMI(Success))) = 0 THEN 'High' ELSE 'Normal' END

Here is the script for the job step above (you will need to update @profile and @recipients parameters with your values):

DECLARE @p_subject NVARCHAR(255), @p_action INT, @p_importance VARCHAR (6)

SELECT @p_action = $(ESCAPE_SQUOTE(WMI(EventSubClass))), 
               @p_importance = CASE WHEN $(ESCAPE_SQUOTE(WMI(Success))) = 0 THEN 'High' ELSE 'Normal' END

SELECT @p_subject = N'WMI Alert 1: Login [$(ESCAPE_SQUOTE(WMI(ObjectName)))] ' + 
		CASE 	WHEN  @p_action = 6 THEN 'enabled on' 
		 	WHEN  @p_action = 5 THEN 'disabled from'
		 	ELSE 'changed on' END + 
		' [$(ESCAPE_SQUOTE(WMI(ComputerName)))\$(ESCAPE_SQUOTE(WMI(SQLInstance)))].' ;

EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'DBServerAlerts', -- update with your values
	@recipients = '[email protected]', -- update with your values
	@importance = @p_importance ,
	@subject = @p_subject,
	@body = N'Time: $(ESCAPE_SQUOTE(WMI(StartTime))); 
ComputerName: $(ESCAPE_SQUOTE(WMI(ComputerName)));
SQL Instance: $(ESCAPE_SQUOTE(WMI(SQLInstance))); 
Target Login Name: $(ESCAPE_SQUOTE(WMI(ObjectName)));
Source Application Name: $(ESCAPE_SQUOTE(WMI(ApplicationName)));
Source Host Name: $(ESCAPE_SQUOTE(WMI(HostName)));
Source Login Name: $(ESCAPE_SQUOTE(WMI(LoginName)));
Source Session Login Name: $(ESCAPE_SQUOTE(WMI(SessionLoginName)));
EventSubClass: $(ESCAPE_SQUOTE(WMI(EventSubClass)));
Success: $(ESCAPE_SQUOTE(WMI(Success)));
';

Setting up WMI Alert to Respond to a Login's Change Events (Enable/Disable Login)

Now we will setup the WMI alert:

Set the alert type to "WMI event alert"
Make sure you use the correct WMI namespace

WMI Alert to Respond to the Login Disable/Enable Events

Note: the namespace will be different for the default instance and for the named instance:

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

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

Here is the WMI query for this alert:

select * from AUDIT_SERVER_PRINCIPAL_MANAGEMENT_EVENT where EventSubClass = 5 or EventSubClass = 6

Read more about the Audit Server Principal Management Event Class here.

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

Create SQL Server Agent Job that will Respond to a Login's Password Changes

Here is the "WMI Response - Audit Login Change Password Event" job's step for the login's password reset/change events response:

"WMI Response - Audit Login Change Password Event" job step

Here is the script for the job step:

DECLARE @p_subject NVARCHAR(255), @p_action NVARCHAR(255), @p_importance VARCHAR (6)

SELECT @p_action = CASE WHEN $(ESCAPE_SQUOTE(WMI(EventSubClass))) = 1 THEN 'Password self changed'
      WHEN $(ESCAPE_SQUOTE(WMI(EventSubClass))) = 2 THEN 'Password changed'
      WHEN $(ESCAPE_SQUOTE(WMI(EventSubClass))) = 3 THEN 'Password self reset'
      WHEN $(ESCAPE_SQUOTE(WMI(EventSubClass))) = 4 THEN 'Password reset'
      WHEN $(ESCAPE_SQUOTE(WMI(EventSubClass))) = 5 THEN 'Password unlocked'
      WHEN $(ESCAPE_SQUOTE(WMI(EventSubClass))) = 6 THEN 'Password must change' ELSE 'N/A password action' END,
               @p_importance = CASE WHEN $(ESCAPE_SQUOTE(WMI(Success))) = 0 THEN 'High' ELSE 'Normal' END

SELECT  @p_subject = N'WMI Alert: ' + @p_action + ' for Login [$(ESCAPE_SQUOTE(WMI(TargetLoginName)))] ' + 
      'on [$(ESCAPE_SQUOTE(WMI(ComputerName)))\$(ESCAPE_SQUOTE(WMI(SQLInstance)))].' ;

EXEC msdb.dbo.sp_send_dbmail
   @profile_name = 'DBServerAlerts', -- update with your values
   @recipients = '[email protected]', -- update with your values
   @importance = @p_importance ,
   @subject = @p_subject,
   @body = N'Time: $(ESCAPE_SQUOTE(WMI(StartTime))); 
ComputerName: $(ESCAPE_SQUOTE(WMI(ComputerName)));
SQL Instance: $(ESCAPE_SQUOTE(WMI(SQLInstance))); 
Target Login Name: $(ESCAPE_SQUOTE(WMI(TargetLoginName)));
Source Application Name: $(ESCAPE_SQUOTE(WMI(ApplicationName)));
Source Host Name: $(ESCAPE_SQUOTE(WMI(HostName)));
Source Login Name: $(ESCAPE_SQUOTE(WMI(LoginName)));
Source Session Login Name: $(ESCAPE_SQUOTE(WMI(SessionLoginName)));
EventSubClass: $(ESCAPE_SQUOTE(WMI(EventSubClass)));
TextData: $(ESCAPE_SQUOTE(WMI(TextData)));
Success: $(ESCAPE_SQUOTE(WMI(Success)));
';

Setting up WMI Alert to Respond to the Login Change Password Event

Now we will create the WMI alert as the following:

Here is the WMI query for this alert:

select * from AUDIT_LOGIN_CHANGE_PASSWORD_EVENT

Read more about the Audit Login Change Password Event Class here.

Note, that the following Event SubClasses are available:

1 - Password self changed
2 - Password changed
3 - Password self reset
4 - Password reset
5 - Password unlocked
6 - Password must change

You can limit the number of alerts if you want and exclude some of the Event SubClasses. For example:

select * from AUDIT_LOGIN_CHANGE_PASSWORD_EVENT where EventSubClass = 2 or EventSubClass = 4 or EventSubClass = 5

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

Create SQL Server Agent Job that will Respond to the Login's Properties Changes (Credentials and Password Settings)

This job ("WMI Response - Audit Login Change Property Event") will respond to the alerts triggered when login's credential is changed or when the password policy and/or expiration is changed:

"WMI Response - Audit Login Change Property Event" job step

Here is the script for the job step:

DECLARE @p_subject NVARCHAR(255), @p_action INT, @p_importance VARCHAR (6)

SELECT @p_action = $(ESCAPE_SQUOTE(WMI(EventSubClass))), 
               @p_importance = CASE WHEN $(ESCAPE_SQUOTE(WMI(Success))) = 0 THEN 'High' ELSE 'Normal' END

SELECT @p_subject = N'WMI Alert: Login''s [$(ESCAPE_SQUOTE(WMI(TargetLoginName)))] ' + 
		CASE 	WHEN  @p_action = 4 THEN 'Credential ' 
		 	WHEN  @p_action = 5 THEN 'Policy ' 
		 	WHEN  @p_action = 6 THEN 'Expiration ' 
		 	ELSE '' END + 
		'property changed on [$(ESCAPE_SQUOTE(WMI(ComputerName)))\$(ESCAPE_SQUOTE(WMI(SQLInstance)))].' ;

EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'DBServerAlerts', -- update with your values
	@recipients = '[email protected]', -- update with your values
	@importance = @p_importance ,
	@subject = @p_subject,
	@body = N'Time: $(ESCAPE_SQUOTE(WMI(StartTime))); 
ComputerName: $(ESCAPE_SQUOTE(WMI(ComputerName)));
SQL Instance: $(ESCAPE_SQUOTE(WMI(SQLInstance))); 
Target Login Name: $(ESCAPE_SQUOTE(WMI(TargetLoginName)));
Source Application Name: $(ESCAPE_SQUOTE(WMI(ApplicationName)));
Source Host Name: $(ESCAPE_SQUOTE(WMI(HostName)));
Source Login Name: $(ESCAPE_SQUOTE(WMI(LoginName)));
Source Session Login Name: $(ESCAPE_SQUOTE(WMI(SessionLoginName)));
EventSubClass: $(ESCAPE_SQUOTE(WMI(EventSubClass)));
Success: $(ESCAPE_SQUOTE(WMI(Success)));
';

Setting up WMI Alert to Respond to the Login's Credentials and Password Settings Change Events

Here is the WMI alert for these login's properties changes:

Here is the WMI query for this alert:

select * from AUDIT_LOGIN_CHANGE_PROPERTY_EVENT where EventSubClass >= 4

If you need to monitor login rename events in addition to other changes you can update the WMI query to this:

select * from AUDIT_LOGIN_CHANGE_PROPERTY_EVENT where EventSubClass >= 3

Find more details about Audit Login Change Property Event Class here.

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

Create SQL Server Agent Job that will Send Alerts when a Login Granted/Denied/Revoked Connect SQL Server Permission

This job ("WMI Response - Audit Server Scope GDR Event") will respond to modifications of the login's permission to connect to the SQL Server:

"WMI Response - AuditServer Scope GDR Event" job step

Here is the script for the job step:

DECLARE @p_subject NVARCHAR(255), @p_action INT, @p_importance VARCHAR (6) 

SELECT @p_action = $(ESCAPE_SQUOTE(WMI(EventSubClass))), 
               @p_importance = CASE WHEN $(ESCAPE_SQUOTE(WMI(Success))) = 0 THEN 'High' ELSE 'Normal' END

SELECT  @p_subject = N'WMI Alert: Login [$(ESCAPE_SQUOTE(WMI(TargetLoginName)))] ' + 
		CASE 	WHEN  @p_action = 1 THEN 'granted ' 
		 	WHEN  @p_action = 2 THEN 'revoked ' 
		 	WHEN  @p_action = 3 THEN 'denied ' 
		 	ELSE '' END + 
		'CONNECT SQL permission on [$(ESCAPE_SQUOTE(WMI(ComputerName)))\$(ESCAPE_SQUOTE(WMI(SQLInstance)))].' ;

EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'DBServerAlerts', -- update with your values
	@recipients = '[email protected]', -- update with your values
	@importance = @p_importance ,
	@subject = @p_subject,
	@body = N'Time: $(ESCAPE_SQUOTE(WMI(StartTime))); 
ComputerName: $(ESCAPE_SQUOTE(WMI(ComputerName)));
SQL Instance: $(ESCAPE_SQUOTE(WMI(SQLInstance))); 
Target Login Name: $(ESCAPE_SQUOTE(WMI(TargetLoginName)));
Source Application Name: $(ESCAPE_SQUOTE(WMI(ApplicationName)));
Source Host Name: $(ESCAPE_SQUOTE(WMI(HostName)));
Source Login Name: $(ESCAPE_SQUOTE(WMI(LoginName)));
Source Session Login Name: $(ESCAPE_SQUOTE(WMI(SessionLoginName)));
EventSubClass: $(ESCAPE_SQUOTE(WMI(EventSubClass)));
TextData: $(ESCAPE_SQUOTE(WMI(TextData)));
Success: $(ESCAPE_SQUOTE(WMI(Success)));
';

Setting up WMI Alert to Respond to the Login's Permissions Changes Events (Connect SQL Only)

WMI alert for the login's permission changes (grant/deny/revoke "CONNECT SQL"):

Alert to audit Grant/Revoke/Deny CONNECT SQL server permission

Note, that "Permissions='1'" filter is used to monitor this specific Server Scope Permission ("CONNECT SQL").

Here is the WMI query for this alert:

select * from AUDIT_SERVER_SCOPE_GDR_EVENT where Permissions = '1'

Read more about the Audit Server Scope GDR Event Class here.

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

Please note, that if access granted/changed using deprecated stored procedures sp_grantlogin, sp_revokelogin or sp_denylogin then you will need to use a different Event Class - "Audit Login GDR":

select * from AUDIT_LOGIN_GDR_EVENT

Testing the Alerts

Now we should be able to receive email notifications every time when a login's property or password has changed or when "CONNECT SQL" Server Level permission has been granted/denied/revoked to/from a login.

Let's run the following commands for our test “_demo_user” login:

USE [master]
GO
ALTER LOGIN [_demo_user] DISABLE
GO

You should get an email like the following:

Note, that the EventSubClass is equal to "5" when login is disabled. If a login is enabled then EventSubClass will be "6" and email's subject will change.

Now we will reset/change this login's password:

USE [master]
GO 
ALTER LOGIN [_demo_user] WITH PASSWORD=N'14523!!!@#qw78eQWE'
GO
ALTER LOGIN [_demo_user] WITH PASSWORD=N'123#qfgfgw78eQWE' OLD_PASSWORD=N'14523!!!@#qw78eQWE'
GO

Here is an email notification for the first command (password reset):

Here is the email for the second command (password change):

Now let's try to specify the incorrect old password:

USE [master]
GO 
ALTER LOGIN [_demo_user] WITH PASSWORD=N'123#qfgfgw78eQWE' OLD_PASSWORD=N'Wrong_Old_Password1'
GO

Note the password change will fail with the following error:

Msg 15151, Level 16, State 1, Line 3
Cannot alter the login '_demo_user', because it does not exist or you do not have permission.

We will get email alert with "High" importance because the password change has failed:

We will now change this login's password settings:

USE [master]
GO
ALTER LOGIN [_demo_user] WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

If we change two properties in the same statement as in the example above (EXPIRATION=OFF, CHECK_POLICY=OFF) then two emails will be sent (one for each property change):

In our next test we will map the login to an existing credential:

USE [master]
GO
ALTER LOGIN [_demo_user] ADD CREDENTIAL [F7C84B65-5090-41C9-ADA9-B510223CA868]
GO

Here is an email notification for this change:

Login property change email (credential settings)

Finally, we will deny permission to connect to the database engine to the login:

USE [master]
GO 
DENY CONNECT SQL TO [_demo_user]
GO

The email looks like this:

Complete Script

The script for all of the jobs and alerts could be downloaded from here.

Please note, that you will need to update the following parts of the script:

@wmi_namespace for the alerts (see the examples above)
@profile_name and @recipients parameters with your values (@profile_name will be the "Mail profile" that you created during Database Mail configuration)
replace the job owner if "sa" login is renamed on your SQL Server:

 @owner_login_name=N'sa'

Next Steps

Refer to the previous tip #1, tip #2, tip #3, tip #4 and tip #5 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

Article Last Updated: 2018-06-19

Automated WMI Alerts for SQL Server Login Property Changes

Problem

Solution

Create SQL Server Agent Job that will Send an Alert when a Login is Enabled/Disabled

Setting up WMI Alert to Respond to a Login's Change Events (Enable/Disable Login)

Create SQL Server Agent Job that will Respond to a Login's Password Changes

Setting up WMI Alert to Respond to the Login Change Password Event

Create SQL Server Agent Job that will Respond to the Login's Properties Changes (Credentials and Password Settings)

Setting up WMI Alert to Respond to the Login's Credentials and Password Settings Change Events

Create SQL Server Agent Job that will Send Alerts when a Login Granted/Denied/Revoked Connect SQL Server Permission

Setting up WMI Alert to Respond to the Login's Permissions Changes Events (Connect SQL Only)

Testing the Alerts

Complete Script

Next Steps

About the author

Comments For This Article