How to add multiple filters on Server Audit of SQL Server?

Question

I want to filter SQL Audits so that I do not want to capture events triggered by certain users and certain schema. In one of the existing Server Audit, I found the filter predicate as

(
[schema_name]<>'sys' AND 
[server_principal_name]<>'SILVER\Distributor' AND 
[server_principal_name]<>'SILVER\Replicator' AND 
[server_principal_name]<>'SILVER\Merger' AND 
[server_principal_name]<>'SILVER\Collecter' AND 
[server_principal_name]<>'SILVER\Reporter' AND 
[server_principal_name]<>'SILVER\Starter' AND 
)

I think it should be OR and not AND. As per TSQL, it looks like above condition will never be satisfied. AND means all of the conditions must be satisfied. I did read the logs using function sys.fn_get_audit_file and did not see any records belonging to above restricted users and schema. It looked like above predicate worked though.

Is AND here acting like a separator of the rules.

Could you please explain this?

Answer 1

You can change your predicate

(
[schema_name]<>'sys' AND 
[server_principal_name]<>'SILVER\Distributor' AND 
[server_principal_name]<>'SILVER\Replicator' AND 
[server_principal_name]<>'SILVER\Merger' AND 
[server_principal_name]<>'SILVER\Collecter' AND 
[server_principal_name]<>'SILVER\Reporter' AND 
[server_principal_name]<>'SILVER\Starter' AND 
)

to its equivalent which uses or

NOT (
[schema_name] = 'sys' OR 
[server_principal_name] = 'SILVER\Distributor' OR 
[server_principal_name] = 'SILVER\Replicator' OR 
[server_principal_name] = 'SILVER\Merger' OR 
[server_principal_name] = 'SILVER\Collecter' OR 
[server_principal_name] = 'SILVER\Reporter' OR 
[server_principal_name] = 'SILVER\Starter'  
)

or even better readable

[schema_name]<>'sys' AND 
[server_principal_name] NOT IN (
'SILVER\Distributor',
'SILVER\Replicator', 
'SILVER\Merger',
'SILVER\Collecter',
'SILVER\Reporter',
'SILVER\Starter' 
)