Alerts and Notifications for SQL Server Login, Database User and Role Membership Changes

By: Svetlana Golovko | Comments (18) | Related: 1 | 2 | 3 | 4 | > Security

Problem

SQL Server security monitoring is a critical part of the Database Administrator's job. Some security related alerts could be setup very easy, but others require third-party tools or extra steps to setup. For example, SQL Server Audit can be used to monitor logins or users modification, but it requires audit log review. We would like to get real-time alerts every time a login or a user is created or added to a server or a database role, how can this be done?

Solution

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

In this tip we will provide steps and scripts for setting up WMI alerts and jobs responding to these alerts to monitor the creation and removal of users and logins as well as server and database roles membership changes.

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

We will provide jobs steps and alerts screenshots and a complete script at the end of the tip for all of the jobs and alerts.

Note - The jobs are not scheduled and cannot be run manually.

Create SQL Server Alert for Create Login and Drop Login Events

In this section we will create a SQL Server Agent Job and an Alert for when logins are created or dropped.

Create SQL Server Job for Create Login and Drop Login Events

The following job ("WMI Response - Audit Add/Remove Login Event") will be responding to the WMI event every time a login is created or deleted.

To create a SQL Server Job, expand SQL Server Agent in SQL Server Management Studio and right click on Jobs and select New Job.

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

Job that will Respond to the Login Creation/Deletion Events

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 

SELECT @p_action = $(ESCAPE_SQUOTE(WMI(EventSubClass)))

SELECT  @p_subject = N'WMI Alert: Login [$(ESCAPE_SQUOTE(WMI(ObjectName)))] ' + 
      CASE WHEN  @p_action = 1 THEN 'created on' 
           WHEN  @p_action = 3 THEN 'dropped 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
   @subject = @p_subject,
   @body = N'Time: $(ESCAPE_SQUOTE(WMI(StartTime))); 
ComputerName: $(ESCAPE_SQUOTE(WMI(ComputerName)));
SQL Instance: $(ESCAPE_SQUOTE(WMI(SQLInstance))); 
Database: $(ESCAPE_SQUOTE(WMI(DatabaseName)));
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)));
';
GO

Create WMI Event for Create Login and Drop Login Events

To create a SQL Server Alert, expand SQL Server Agent in SQL Server Management Studio and right click on Alerts and select New Alert.

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 Creation/Deletion Events

Note: The namespace will be different for the default instance and for the named instance. Here are some examples.

-- 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= 1 or EventSubClass = 3

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

Alert to Respond to the Login Creation/Deletion Events - response

Create SQL Server Alert for Add Member and Drop Member Server Role Events

In this section we will create a SQL Server Agent Job and an Alert for when logins are added or dropped from SQL Server server roles.

Create SQL Server Job for Add Member and Drop Member Server Role Events

Here is the "WMI Response - Audit Add/Remove Server Role Member Event " job's step for the server roles membership changes monitoring response:

Job that will Respond to the Server Roles Add/Remove Logins Events

Here is the script for the job step:

DECLARE @p_subject NVARCHAR(255), @p_action INT 

SELECT @p_action = $(ESCAPE_SQUOTE(WMI(EventSubClass)))

SELECT @p_subject = N'WMI Alert: Login [$(ESCAPE_SQUOTE(WMI(TargetLoginName)))] ' + 
      CASE WHEN  @p_action = 1 THEN 'added to the' 
           WHEN @p_action = 2 THEN 'removed from the' 
           ELSE 'changed on' 
      END + 
      ' [$(ESCAPE_SQUOTE(WMI(RoleName)))] Server Role 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
   @subject = @p_subject,
   @body = N'Time: $(ESCAPE_SQUOTE(WMI(StartTime))); 
Computer Name: $(ESCAPE_SQUOTE(WMI(ComputerName)));
SQL Instance: $(ESCAPE_SQUOTE(WMI(SQLInstance))); 
Database: $(ESCAPE_SQUOTE(WMI(DatabaseName)));
Target Login Name: $(ESCAPE_SQUOTE(WMI(TargetLoginName)));
Target Server Role Name: $(ESCAPE_SQUOTE(WMI(RoleName)));
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)));
SQL Statement: $(ESCAPE_SQUOTE(WMI(TextData)));
EventSubClass: $(ESCAPE_SQUOTE(WMI(EventSubClass)));
';

Create WMI Event for Add Member and Drop Member Server Role Events

Now we will create the WMI alert as following:

Alert to Respond to the Server Roles Add/Remove Logins Events

Here is the WMI query for this alert:

select * from AUDIT_ADD_LOGIN_TO_SERVER_ROLE_EVENT

If you want to audit only "sysadmin" role membership changes you can update the alert's WMI query above with this:

select * from AUDIT_ADD_LOGIN_TO_SERVER_ROLE_EVENT where RoleName='sysadmin'

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

Alert to Respond to the Server Roles Add/Remove Logins Events - response

Create SQL Server Alert for Create User and Drop User Events

In this section we will create a SQL Server Agent Job and an Alert for when users are created or dropped for a database.

Create SQL Server Job for Create User and Drop User Events

This job ("WMI Response - Audit Add/Remove Database User Event") will respond to the alerts triggered when a user in a database is created or if a user is deleted from the database:

Job that will Respond to the User Creation/Deletion Events

Here is the script for the job step:

DECLARE @p_subject NVARCHAR(255), @p_action INT 

SELECT @p_action = $(ESCAPE_SQUOTE(WMI(EventSubClass)))

SELECT  @p_subject = N'WMI Alert: Database User [$(ESCAPE_SQUOTE(WMI(TargetUserName)))] ' + 
      CASE WHEN  @p_action = 3 THEN 'added to' 
           WHEN @p_action = 4 THEN 'removed from' 
           WHEN @p_action = 1 THEN 'added to the [$(ESCAPE_SQUOTE(WMI(RoleName)))] Database Role on'  
           WHEN @p_action = 2 THEN 'removed from the [$(ESCAPE_SQUOTE(WMI(RoleName)))] Database Role on' 
           ELSE 'changed on' 
      END + 
      ' [$(ESCAPE_SQUOTE(WMI(ComputerName)))\$(ESCAPE_SQUOTE(WMI(SQLInstance)))]:[$(ESCAPE_SQUOTE(WMI(DatabaseName)))].' ;

EXEC msdb.dbo.sp_send_dbmail
   @profile_name = 'DBServerAlerts', -- update with your values
   @recipients = '[email protected]', -- update with your values
   @subject = @p_subject,
   @body = N'Time: $(ESCAPE_SQUOTE(WMI(StartTime))); 
Computer Name: $(ESCAPE_SQUOTE(WMI(ComputerName)));
SQL Instance: $(ESCAPE_SQUOTE(WMI(SQLInstance))); 
Database: $(ESCAPE_SQUOTE(WMI(DatabaseName)));
Target Login Name: $(ESCAPE_SQUOTE(WMI(TargetLoginName)));
Target DB User Name: $(ESCAPE_SQUOTE(WMI(TargetUserName)));
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)));
';

Create WMI Event Job for Create User and Drop User Events

Here is the WMI alert for the database users creation or deletion:

WMI Alert to Respond to the User Creation/Deletion Events

Here is the WMI query for this alert:

select * from AUDIT_ADD_DB_USER_EVENT

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

Alert to Respond to the User Creation/Deletion Events - response

Create SQL Server Alert for Add Member and Drop Member Database Role Events

In this section we will create a SQL Server job and an Alert for when users are added or dropped from database roles.

Create SQL Server Job for Add Member and Drop Member Database Role Events

This job ("WMI Response - Audit Add/Remove DB Role Member Event") will respond to the database roles membership modification events:

Job that will Respond to the Database Roles Add/Remove User Events

Here is the script for the job step:

DECLARE @p_subject NVARCHAR(500), @p_action INT 

SELECT @p_action = $(ESCAPE_SQUOTE(WMI(EventSubClass)))

SELECT  @p_subject = N'WMI Alert: User [$(ESCAPE_SQUOTE(WMI(TargetLoginName)))] ' + 
      CASE WHEN  @p_action = 1 THEN 'added to the' 
           WHEN @p_action = 2 THEN 'removed from the' 
           ELSE 'changed on' 
      END + 
      ' [$(ESCAPE_SQUOTE(WMI(RoleName)))] Database Role on [$(ESCAPE_SQUOTE(WMI(DatabaseName)))]:[$(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
   @subject = @p_subject,
   @body = N'Time: $(ESCAPE_SQUOTE(WMI(StartTime))); 
Computer Name: $(ESCAPE_SQUOTE(WMI(ComputerName)));
SQL Instance: $(ESCAPE_SQUOTE(WMI(SQLInstance))); 
Database: $(ESCAPE_SQUOTE(WMI(DatabaseName)));
Target User Name: $(ESCAPE_SQUOTE(WMI(TargetUserName)));
Target Database Role Name: $(ESCAPE_SQUOTE(WMI(RoleName)));
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)));

Create WMI Event for Add Member and Drop Member Database Role Events

WMI alert for the database roles membership changes:

WMI Alert to Respond to the Database Roles Add/Remove User Events

Here is the WMI query for this alert:

select * from AUDIT_ADD_MEMBER_TO_DB_ROLE_EVENT

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

Alert to Respond to the Database Roles Add/Remove User Events - response

Testing SQL Server Security Alerts

Now we should be able to receive email notifications every time somebody creates or deletes logins or database users or adds or removes them from server or database roles.

Let's create a test login:

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

You should get an email as the following one:

Now we will add this login to a server role:

USE [master]
GO
ALTER SERVER ROLE [bulkadmin] ADD MEMBER [_demo_user]
GO

Here is an email notification:

We will add this login as a database user on the Contoso database:

USE [Contoso]
GO
CREATE USER [_demo_user] FOR LOGIN [_demo_user]
GO

Here is the email:

Now, we will add this user to the db_datareader database role:

USE [Contoso]
GO
ALTER ROLE [db_datareader] ADD MEMBER [_demo_user]
GO

The email looks like this:

Removing a user from a database role:

USE [Contoso]
GO
ALTER ROLE [db_datareader] DROP MEMBER [_demo_user]
GO

Here is an email example:

User removed from the database role email

Let's remove the user from the database:

USE [Contoso]
GO
DROP USER [_demo_user]
GO

Here is the email:

Now, let's remove the login from the server role:

USE [master]
GO
ALTER SERVER ROLE [bulkadmin] DROP MEMBER [_demo_user]
GO

Here is the email:

Login removed from the server role email

And, finally, delete the login completely from the SQL Server:

USE [master]
GO
DROP LOGIN [_demo_user]
GO

Here is the email:

Complete Script

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

Please note, that you may 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 in the script if the "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 and tip #4 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.
Please note, that if you still use deprecated stored procedures sp_grantlogin, sp_revokelogin, and sp_denylogin stored procedures you will need to use in addition to the alerts above the AUDIT_LOGIN_GDR_EVENT Event Class for the monitoring logins creation/deletion. This event class may be removed in a future version of SQL Server: https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/audit-login-gdr-event-class.

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

Friday, December 2, 2022 - 1:29:57 AM - DBA	Back To Top (90745)
I'm getting the below error, Can someone help me here? Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'ESCAPE_SQUOTE'. Completion time: 2022-12-02T14:28:33.2040685+08:00

Thursday, September 15, 2022 - 10:24:43 PM - ganesh	Back To Top (90488)
I followed all the steps and its working for all except for USE -- GO ALTER ROLE [--] ADD MEMBER [--] GO Receiving email CAUTION: This email is from an external source. '--' Please beware of links and attachments. DATE/TIME: 9/15/2022 10:21:59 PM DESCRIPTION: (None) COMMENT: (None) JOB RUN: Audit Add/Remove DB Role Member Event Please help

Sunday, May 22, 2022 - 4:29:52 AM - Ajay Mane	Back To Top (90101)
Thanks for the post it is very helpful.

Tuesday, September 24, 2019 - 5:24:17 AM - Andrew Robinson	Back To Top (82552)
Nice article, thanks. I am no SQL expert so hope these comments make sense. It would be useful to have a more obvious indication that you actually have to set up auditing and what one would need to specify in the Server Audit Specification properties to capture these events. Also I found it confusing that the 'send email repsonse' Job step script was repeated for each alert.

Alerts and Notifications for SQL Server Login, Database User and Role Membership Changes

Problem

Solution

Create SQL Server Alert for Create Login and Drop Login Events

Create SQL Server Job for Create Login and Drop Login Events

Create WMI Event for Create Login and Drop Login Events

Create SQL Server Alert for Add Member and Drop Member Server Role Events

Create SQL Server Job for Add Member and Drop Member Server Role Events

Create WMI Event for Add Member and Drop Member Server Role Events

Create SQL Server Alert for Create User and Drop User Events

Create SQL Server Job for Create User and Drop User Events

Create WMI Event Job for Create User and Drop User Events

Create SQL Server Alert for Add Member and Drop Member Database Role Events

Create SQL Server Job for Add Member and Drop Member Database Role Events

Create WMI Event for Add Member and Drop Member Database Role Events

Testing SQL Server Security Alerts

Complete Script

Next Steps

About the author

Comments For This Article