Auditing selective users

Question

Post reply

Auditing selective users

oskargunn

SSCommitted

Points: 1951
More actions
October 2, 2019 at 10:52 am

#3685234
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.

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

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

oskargunn SSCommitted Points: 1951 More actions · Answer 1

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

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

oskargunn SSCommitted Points: 1951 More actions · Answer 3

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 ?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

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

oskargunn SSCommitted Points: 1951 More actions · Answer 5

Thanks for your suggestion.

I'll try to use filter and test performance.