SQL Server comes with a build in audit functionality that saves a lot of development effort when one of the business requirements states is to keep a record of user activity in the database.
For each query executed in the database, SQL Server Audit captures the names of the affected tables, SQL statement used for query, whether query execution was successful and whether or not user had permission to access particular table.
The deployment is very easy and straight forward. It starts from defining new SERVER AUDIT object:
USE [master]
GO
CREATE SERVER AUDIT [ServerAuditTest]
TO FILE
( FILEPATH =N’C:MSSQLData’
,MAXSIZE = 10MB
,MAX_ROLLOVER_FILES = 10
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY =1000
,ON_FAILURE = CONTINUE
)
Script defines storage mode for the log data (file or application log), max size of audit files, max number of files and whether SQL should function if audit fails to start. Once created, audit object must be enabled by executing the following script:
ALTER SERVER AUDIT [ServerAuditTest]
WITH (STATE = ON);
Database audit specification object is attached to the audit object created earlier. It defines a combination of actions (events), securables (database objects) and principles (users or roles) that should be audited.
The following script defines audit for all users that belong to “DatabaseRoleWithAudit” database role executing Delete, Insert, Select, Update or Execute commands against any objects owned by dbo schema:
USE[DatabaseName]
GO
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditTest]
FOR SERVER AUDIT [ServerAuditTest]
ADD (DELETE ONSCHEMA::[dbo] BY [DatabaseRoleWithAudit]),
ADD (EXECUTE ON SCHEMA::[dbo] BY [DatabaseRoleWithAudit]),
ADD (INSERT ON SCHEMA::[dbo] BY [DatabaseRoleWithAudit]),
ADD (SELECT ON SCHEMA::[dbo] BY [DatabaseRoleWithAudit]),
ADD (UPDATE ON SCHEMA::[dbo] BY [DatabaseRoleWithAudit])
WITH (STATE = ON)
If created with State=ON, audit starts working right away.
Querying data collected by audit is very simple. You can do it through SQL Server Management Studio by navigating to “Security Audit Audit Name”, clicking right mouse and selecting “View log” or by query audit logs in Query Analyser using the following command:
SELECT
*
FROM
sys.fn_get_audit_file(N’C:MSSQLDataServerAuditTest*.sqlaudit’, null, null)
In the script above I defined that audit logs should be stored as rollover files, so it makes sense to add a SQL job that would query audit logs on a scheduled basis and move new records from log into a permanent table where it can be indexed for better query performance and made available to the users for analysis.