SQL SERVER – Script to Audit Login and Role Member Change

Once there was a security breach on my client machine and they wanted to know who made changes to the SysAdmin role. They contacted me and my first question was that, are you are auditing such changes and as expected answer was No. I gave them answer that nothing can be done as a consultant. They still wanted my suggestion to prevent this in future. So, they want to track if someone does such things in future. Let us see a script to audit login and role member change.

Here is the solution I provided them and here I am sharing with everyone. I provided them script to create Audit and added those events which are useful to trace such issues. Creating a new Login, Adding a Login to a role would be tracked using below.

WORKAROUND/SOLUTION

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

TESTING

To test the audit defined above, we create a new login and added that login to 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

And here is what we see in Audit logs.

This fulfilled their request and they could find the process which was doing it.

Do you use Audit in a production environment? If yes, is it a native SQL audit or some 3^rd party solution?

Reference: Pinal Dave (http://blog.SQLAuthority.com)