SQL Server – Applying Multiple Filters for Server Level Audit

auditsql serversql server 2014

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

Best 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.

Related Solutions

Sql-server – SQL Server Audit not tracking changes at the database level

I think the main problem here - and please don't take offense - is that you're pointing and clicking in a GUI but not really sure what you're pointing and clicking at.

Here is an example that creates a server-level audit, then adds a database-level audit specification to track multiple operations on any object in the dbo schema.

USE master;
GO

-- create aserver audit
CREATE SERVER AUDIT Test_Server_Audit 
  TO FILE ( FILEPATH = 'C:\temp\' ); -- you may need to change that'
GO

-- turn it on
ALTER SERVER AUDIT Test_Server_Audit WITH (STATE = ON);
GO

-- create a demo database    
CREATE DATABASE floob;
GO
USE floob;
GO
CREATE TABLE dbo.blat(x INT);
GO

-- create a database audit specification that monitors for activity
-- against any dbo object:
CREATE DATABASE AUDIT SPECIFICATION Test_Database_Audit
    FOR SERVER AUDIT Test_Server_Audit
    ADD (SELECT, UPDATE, DELETE, INSERT, EXECUTE ON SCHEMA::dbo BY PUBLIC)
    WITH (STATE = ON);
GO

-- do a couple of things:
SELECT * FROM dbo.blat;
DELETE dbo.blat;
GO

-- you should see those couple of things in the audit file:
SELECT * FROM sys.fn_get_audit_file('C:\temp\*.sqlaudit', NULL, NULL);
GO

Now, clean up:

ALTER DATABASE AUDIT SPECIFICATION Test_Database_Audit
    WITH (STATE = OFF);
GO
DROP DATABASE AUDIT SPECIFICATION Test_Database_Audit;
GO
USE master;
GO
ALTER DATABASE floob SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE floob;
GO

ALTER SERVER AUDIT Test_Server_Audit
    WITH (STATE = OFF);
GO
DROP SERVER AUDIT Test_Server_Audit;
GO

I'd start at perhaps a higher conceptual level before pressing a bunch of buttons and hoping that they work.

SQL Server Database Existence – Troubleshooting Database Creation Issues

Sounds like someone changed the defaults that exist in the model database on the server (512 is usually the default from what I've seen--not 4096). Since this is for a deployment on possibly any SQL Server (where model specifications could well be different) just remove the SIZE = 4096KB in both the mdf and ldf sections--to avoid this issue altogether. Each SQL Server will create the database based on specifications of the model database on SQL Server.

Example:

CREATE DATABASE [BIDS] CONTAINMENT = NONE 
ON PRIMARY ( NAME = N'BIDS', FILENAME = N'C:\temp\BIDS.mdf' ,  
  MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) 
LOG ON ( NAME = N'BIDS_log', FILENAME = N'C:\temp\BIDS_log.ldf' ,  
  MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)

Best Answer

Related Solutions

Sql-server – SQL Server Audit not tracking changes at the database level

SQL Server Database Existence – Troubleshooting Database Creation Issues

Related Question