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