Have you ever encountered a situation where you needed to track changes made to the SysAdmin role in SQL Server? In this article, we will discuss a solution to audit login and role member changes, allowing you to identify any unauthorized modifications.

Recently, I had a client who experienced a security breach and wanted to determine who made changes to the SysAdmin role. Unfortunately, they did not have auditing enabled to track such changes. While I initially thought there was nothing that could be done, they still wanted my suggestion to prevent similar incidents in the future.

Here is the solution I provided them, which can be useful for anyone facing a similar situation:

USE MASTER
GO

-- Create the server audit
CREATE SERVER AUDIT login_perm_audit
TO FILE (FILEPATH ='E:\DATA\');
GO

-- Enable the server audit
ALTER SERVER AUDIT login_perm_audit 
WITH (STATE = ON);
GO

-- Create the server audit specification
CREATE SERVER AUDIT SPECIFICATION login_audit_spec
FOR SERVER AUDIT login_perm_audit
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP)
WITH (STATE = ON);
GO

The script above creates a server audit named “login_perm_audit” and specifies the events to be tracked. In this case, we are interested in tracking server permission changes, server principal changes, and server role member changes.

To test the audit, we can create a new login and add it to the SysAdmin role:

USE [master]
GO

CREATE LOGIN [HackerLogin] WITH PASSWORD = N'P@$$@w0rd123', DEFAULT_DATABASE = [master], CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [HackerLogin]
GO

After executing the above script, we can check the audit logs to see the recorded events:

Date: 04-Apr-17 3:49:36 AM
Log: Audit Collection (login_perm_audit)
Event Time: 03:49:36.6885017
Server Instance Name: SQLAUTHORITY\SQL2016
Action ID: CREATE
Class Type: SQL LOGIN
Server Principal Name: sa
Database Name: master
Object Name: HackerLogin
Statement: CREATE LOGIN

Date: 04-Apr-17 3:49:39 AM
Log: Audit Collection (login_perm_audit)
Event Time: 03:49:39.0882101
Server Instance Name: SQLAUTHORITY\SQL2016
Action ID: ADD MEMBER
Class Type: SERVER ROLE
Server Principal Name: sa
Target Server Principal Name: HackerLogin
Object Name: sysadmin
Statement: ALTER SERVER ROLE [sysadmin] ADD MEMBER [HackerLogin]

By reviewing the audit logs, my client was able to identify the process responsible for the unauthorized changes.

Do you use auditing in your production environment? If so, is it a native SQL audit or a third-party solution? Let us know in the comments below!