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:\MSSQL\Data\'
,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 ON
SCHEMA::[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:\MSSQL\Data\ServerAuditTest*.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.
