SQL SERVER – Script to Audit Login and Role Member Change

SQL SERVER - Script to Audit Login and Role Member Change dbaudit 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.

Date04-Apr-17 3:49:36 AM
LogAudit Collection (login_perm_audit)
Event Time03:49:36.6885017
Server Instance NameSQLAUTHORITY\SQL2016
Action IDCREATE
Class TypeSQL LOGIN
Server Principal Namesa
Database Namemaster
Object NameHackerLogin
StatementCREATE LOGIN

 

Date04-Apr-17 3:49:39 AM
LogAudit Collection (login_perm_audit)
Event Time03:49:39.0882101
Server Instance NameSQLAUTHORITY\SQL2016
Action IDADD MEMBER
Class TypeSERVER ROLE
Server Principal Namesa
Target Server Principal NameHackerLogin
Object Namesysadmin
StatementALTER SERVER ROLE [sysadmin] ADD MEMBER [HackerLogin]

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 3rd party solution?

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

, , ,
Previous Post
SQL SERVER – Msg 3136, Level 16 – This Differential Backup Cannot be Restored Because the Database has not Been Restored to the Correct Earlier State
Next Post
SQL SERVER – Unable to recycle Errorlog – sp_cycle_errorlog – OS error 1392

Related Posts

1 Comment. Leave new

  • Hello – This was Extremely helpful … Thanks!!

    How could I receive an email notification when the audit is triggered?

    Thanks,
    Terry Brothers

    Reply

Leave a Reply

Menu