Multiple Filters for Databases on SQL Server Server Level Audit

Posted on

Question :

I am attempting to create a Server Level Audit to track Database Level events in SQL Server 2014. I know I can create them at the database level but I want the audits all databases at the server level if this is possible.

I need to filter the Server Level Audit for two databases and only for users who are not the two application users but my filter is not working correctly. Either I am building the filter wrong or it is not possible to filter multiple database and multiple users. My Google-Fu is failing me in finding examples with multiple filters:

Any assistance or ideas are appreciated.

CREATE SERVER AUDIT [PCIAudit]
TO FILE
(   FILEPATH = N'D:SQLAuditLogs'
    ,MAXSIZE = 2048 MB
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 0
    ,ON_FAILURE = CONTINUE
)
WHERE ([database_name] = 'DataBase1' OR [database_name] = 'DataBase2' AND [server_principal_id] <> 277 OR [server_principal_id] <> 278 AND NOT [statement] like 'ALTER INDEX%REBUILD%' AND NOT [statement] like 'ALTER INDEX%REORGANIZE%')
GO

ALTER SERVER AUDIT [PCIAudit]
WITH (STATE = ON);
GO



USE [master]

GO

CREATE SERVER AUDIT SPECIFICATION [DDLAudit]
FOR SERVER AUDIT [PCIAudit]
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (DATABASE_OBJECT_ACCESS_GROUP)
WITH (STATE = ON)
GO

Answer :

Try this, which slightly modifies your WHERE clause to correctly follow the SQL Server operator precedence rules:

CREATE SERVER AUDIT [PCIAudit]
TO FILE
(   FILEPATH = N'D:SQLAuditLogs'
    , MAXSIZE = 2048 MB
    , RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 0
    , ON_FAILURE = CONTINUE
)
WHERE ([database_name] = 'DataBase1' OR [database_name] = 'DataBase2') 
    AND [server_principal_id] <> 277 
    AND [server_principal_id] <> 278 
    AND NOT [statement] LIKE 'ALTER INDEX%REBUILD%' 
    AND NOT [statement] LIKE 'ALTER INDEX%REORGANIZE%';
GO

ALTER SERVER AUDIT [PCIAudit]
WITH (STATE = ON);
GO

Notice the round brackets ( and ) in this: ([database_name] = 'DataBase1' OR [database_name] = 'DataBase2') – they tell the where clause to allow either DataBase1 or Database2. As you have it, the where clause is set to allow either DataBase1 or Database2+server_principal_id<>277.

Leave a Reply

Your email address will not be published.