ALTER SERVER AUDIT -- need to supply 1 predicate with 2 values to filter on

Question

Post reply

ALTER SERVER AUDIT -- need to supply 1 predicate with 2 values to filter on

Express12

SSCoach

Points: 18514
More actions
February 7, 2024 at 4:44 pm

#4374880

Looking for example FILTER containing multiple values in the predicate -- to apply to "ALTER SERVER AUDIT"
Need to filter on 2 SERVER_PRINCIPAL_NAMES which I do not want captured in my DATABASE AUDIT (EG. thousands of rows of activity for Idera's SQLdm ID: 'mydomain\!sqlmonitoring' and our standard service account performing the DML 'myDomain\myServiceAccount')
An internet search on this has resulted in limited examples. I have applied the following predicate for server_principal_name:
ALTER SERVER AUDIT My_Server_Audit WHERE server_principal_name = N'mydomain\!sqlmonitoring' OR server_principal_name = N'myDomain\myServiceAccount'
GO
The above ALTER executes clean BUT I'm still seeing values in my audit file for both server_principal_name values = 'mydomain\!sqlmonitoring' AND 'myDomain\myServiceAccount'
any feedback is appreciated..

BT

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

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

If you want to exclude them, then you need a NOT operator instead of an EQUALS operator.

WHERE server_principal_name <> 'MyDomain\MyUser'

Express12 SSCoach Points: 18514 More actions · Answer 2

Still a tad bit confused Mr. Ant ... I'd like to exclude 2 values found in server_principal_name from being COLLECTED: mydomain\!sqlmonitoring --AND-- myDomain\myServiceAccount

This ALTER is working great for 1 value (all rows containing 'mydomain\!sqlmonitoring' are excluded):

ALTER SERVER AUDIT Audit_MyServer_DBs WHERE server_principal_name<>N'mydomain\!sqlmonitoring'

GO

How would one CODE the above ALTER to exclude BOTH values from being collected? ( mydomain\!sqlmonitoring --AND-- myDomain\myServiceAccount ) the actual T-SQL would be greatly appreciated!!

BT

ratbak Ten Centuries Points: 1358 More actions · Answer 3

NOT IN -- i.e.,

server_principal_name NOT IN (N'mydomain\!sqlmonitoring',N'myDomain\myServiceAccount')

Express12 SSCoach Points: 18514 More actions · Answer 4

therein lies the problem, when I specify:

ALTER SERVER AUDIT Audit_MyServer_DBs WHERE server_principal_name NOT IN (N'myDomain\!sqlmonitoring', N'myDomain\myDomain\myServiceAccount')

I receive error:

Msg 156, Level 15, State 1, Line 198

Incorrect syntax near the keyword 'IN'.

BT

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

You can try one of the following:

WHERE server_principal_name <> 'myDomain\!sqlmonitoring' AND server_principal_name <> 'myDomain\myDomain\myServiceAccount''
WHERE NOT (server_principal_name = 'myDomain\!sqlmonitoring' OR server_principal_name = 'myDomain\myDomain\myServiceAccount'')

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Ant-Green SSC Guru Points: 113507 More actions · Answer 6

Yeah what Jeffrey said.

Audit you need to be very explicit each new clause must be its own individual filter.

(
    [server_principal_name] <> 'MyDomain\User1'
    AND
    [server_principal_name] <> 'MyDomain\User2'
)

Also I forgot to ask why are you auditing?

Is this something for compliance? Something for legally binding information, like HIPPA/GDPR etc etc, Person X looked at Data Y and was the only one who did between dates A and B which resulted in data breach Z?

If you are auditing for any legal reasons then you absolutely should not be using SQL Audit, you need an appliance like Impervia or Guardium to sit in between the network and SQL and capture whats happening.

If it's not legally binding auditing then yeah but just remember SQL Audit is not tamperproof, anyone with enough access can hide their tracks with SQL Audit.