Auditing selective users

  • Hi,

    I am putting together sql server auditing for a certain project.

    I create a sql server audit object (which writes audits to file). Then I create a database audit specification. First I used public as principal

    CREATE DATABASE AUDIT specification [DBAuditSpec] FOR SERVER AUDIT [DBAudit] ADD (SELECT, INSERT, UPDATE, DELETE, EXECUTE, RECEIVE, REFERENCES ON DATABASE::MyDb BY public) WITH (STATE = ON);

    However I have changed my mind, since lots of inserts are performed by certain local sql logins, which I don't want audited.

    So then I wanted only Windows Users and Windows Groups as principals in the auditing spec.

    However if someone i part of an administration group and has sysadmin rights, their actions are then not audited. Is there a way to rectify that ?

    CREATE DATABASE AUDIT specification [DBAuditSpec] FOR SERVER AUDIT [DBAudit] ADD (SELECT, INSERT, UPDATE, DELETE, EXECUTE, RECEIVE, REFERENCES ON DATABASE::MyDb BY [domain\user1],[domain\group1]) WITH (STATE = ON);

    I have also tried putting dbo instead og public.

     

  • N.B. The group that has sysadmin rights is not present in the database.

  • I think you'll need to capture actions from all principals and then filter out the stuff you don't want at server audit level.  Here's an example:

    CREATE SERVER AUDIT MyServerAudit
    TO FILE
    (FILEPATH = N'...\MSSQL\Log\'
    ,MAXSIZE = 80 MB
    ,MAX_ROLLOVER_FILES = 5
    ,RESERVE_DISK_SPACE = ON
    )
    WITH
    (QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = '3267499e-2d17-4e83-b257-e28d5964283e'
    )
    WHERE (
    [action_id]<>(1414746966) AND
    [action_id]<>(1414743126) AND NOT
    [server_principal_name]='MyDomain\SQLServiceAccount' AND NOT
    ([server_principal_name]='MyDomain\SQLAgentServiceAccount' AND [statement] like 'ALTER INDEX%') AND NOT
    ([server_principal_name]='MyDomain\SQLAgentServiceAccount' AND [action_id]=(538984770)) AND NOT
    [server_principal_name]='NT SERVICE\SQLTELEMETRY')
    ALTER SERVER AUDIT [SQLUtilsAudit] WITH (STATE = ON);

    John

  • Do you know how much impact on performance this has on the server to audit on public and then filter the result in the server audit ?

  • No, but I'd be surprised if you noticed it.  You're filtering before writing to disk, so I think it's quite a lightweight process.

    John

  • Thanks for your suggestion.

    I'll try to use filter and test performance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply