Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Best Practices for Migrating SQL Server to Azure - free webinar
 

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


By:   |   Last Updated: 2018-04-11   |   Comments   |   Related Tips: More > 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.

SQL Server AGent Jobs and alerts

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'[email protected]', 
   DEFAULT_DATABASE=[master], 
   CHECK_EXPIRATION=ON, 
   CHECK_POLICY=ON
GO	

You should get an email as the following one:

Login created email

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:

Login added to the server role email

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:

User created 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:

User added to the database role email

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:

User deleted 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:

Login deleted 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


Last Updated: 2018-04-11


next webcast button


next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools