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

By: Svetlana Golovko   |   Updated: 2018-04-11   |   Comments (22)   |   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):

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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