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

oskargunn

SSCommitted

Points: 1951

October 2, 2019 at 10:53 am

#3685235

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

John Mitchell-245523 SSC Guru Points: 148786 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);