0

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.

3

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

I suggest looking at the documentation for examples using DDL (e.g. CREATE SERVER AUDIT and CREATE SERVER AUDIT SPECIFICATION). This wasn't too hard to work out:

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:

enter image description here

  • I was able to create the audit suited to my requirements using the script but is there any reason why SERVER_PRINCIPAL_CHANGE_GROUP audit specification was not made visible through GUI ? Is that intentional by Microsoft or am I missing anything here? – karun_r Mar 10 '16 at 3:37
  • @karun10 I still don't know where in the GUI you were looking. It will be on the audit specification dialog, not the audit dialog. Post a screen shot of where you are looking and expecting to find it - my guess is you are expecting a drop down on the audit dialog, but this isn't where it goes... – Aaron Bertrand Mar 10 '16 at 6:48
  • Yes. That's where I was looking at, the drop down in the audit specification. So, if we need to create a audit to capture the login information, the only way to do this is using the T-SQL, right ? We cannot do this from GUI I guess then. – karun_r Mar 11 '16 at 3:00
  • @karun10 No, you can see it in the GUI (evidence here), again you need to clarify (maybe post a screen shot, like I did). – Aaron Bertrand Mar 11 '16 at 3:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.