4

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
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.