I am trying to use 'SERVER_PRINCIPAL_CHANGE_GROUP' audit action on a SQL Server to capture the sp_addlogin events. But when I try to create a Server level audit using GUI, I am not seeing the Audit action type drop down when I try to create the audit. Any help on how to do this ? I just want to capture who added a server level login using the SQL Server audits.
We can't troubleshoot pointing and clicking through a GUI, because we don't know exactly what you did or what you might have missed.
You need to create a server level audit (which is like a container for specific audit definitions) and a server level audit specification (which is the part that dictates the auditing).
USE master; GO CREATE SERVER AUDIT ServerAudit TO FILE ( FILEPATH = N'C:\temp\', MAXSIZE = 0 MB, MAX_ROLLOVER_FILES = 2147483647, RESERVE_DISK_SPACE = OFF ) WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE, AUDIT_GUID = '0da5fd7b-8218-486f-8641-c31465cae657' ); GO ALTER SERVER AUDIT [ServerAudit] WITH (STATE = ON); GO CREATE SERVER AUDIT SPECIFICATION [AuditPrincipals] FOR SERVER AUDIT [ServerAudit] ADD (SERVER_PRINCIPAL_CHANGE_GROUP) WITH (STATE = ON); GO
So then I did this:
CREATE LOGIN floob WITH PASSWORD = 'blat', CHECK_POLICY = OFF;
And I saw this in the audit log: