Introduction
Auditing in SQL Server helps track changes and operations for compliance, troubleshooting, and security. In this guide, we’ll walk through creating a Server Audit and Database Audit Specification to capture INSERT, UPDATE, DELETE (DML) and DDL changes on a specific database. We’ll also show how to automate loading audit data into a table for reporting.
Step 1: Create a Server Audit
-- The Server Audit defines where audit logs will be stored. Here, we store them in a file:
CREATE SERVER AUDIT Audit_DDLDML_Ops
TO FILE (FILEPATH = 'I:\Audit_AllOps\', MAXSIZE = 10240 MB, MAX_FILES = 10, RESERVE_DISK_SPACE = OFF)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
-- Enable the Server Audit
ALTER SERVER AUDIT Audit_DDLDML_Ops WITH (STATE = ON);
Show more lines
Explanation:
FILEPATH→ Location for audit files.MAXSIZE→ Maximum size per file.ON_FAILURE = CONTINUE→ Continue operations even if audit fails.
Step 2: Create Database Audit Specification
-- This links the database to the server audit and specifies what actions to capture:
USE DBA;
CREATE DATABASE AUDIT SPECIFICATION Audit_DDLDML_Operations
FOR SERVER AUDIT Audit_DDLDML_Ops
ADD (INSERT ON DATABASE::DBA BY PUBLIC),
ADD (UPDATE ON DATABASE::DBA BY PUBLIC),
ADD (DELETE ON DATABASE::DBA BY PUBLIC),
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
Show more lines
Explanation:
- Captures INSERT, UPDATE, DELETE for all tables.
SCHEMA_OBJECT_CHANGE_GROUP→ Captures DDL changes like CREATE, ALTER, DROP.BY PUBLIC→ Applies to all users.
Step 3: Automate Audit Data Loading
Create a SQL Agent Job to load audit data into a reporting table:
INSERT INTO [dbo].[SQLALLOpsAUDIT_HISTORY]
(event_time, application_name, host_name, object_name, action_id, server_principal_name, server_instance_name, database_name, statement)
SELECT event_time, application_name, host_name, object_name, action_id, server_principal_name, server_instance_name, database_name, statement
FROM fn_get_audit_file('I:\Audit_AllOps\Audit-*.sqlaudit', DEFAULT, DEFAULT)
WHERE Action_id IN ('LGIF','CR','AL','BA','IN','DL','DR','UP')
AND event_time >= DATEADD(HOUR, -24, GETDATE());
Show more lines
Explanation:
fn_get_audit_filereads audit files.- Filters for last 24 hours and specific actions.
Step 4: Monitor and Manage Audits
-- Check active audits:
SELECT name, is_state_enabled FROM sys.server_audits;
SELECT name, is_state_enabled FROM sys.database_audit_specifications;
-- Disable and drop audits when no longer needed:
USE DBA
ALTER DATABASE AUDIT SPECIFICATION [Audit_DDLDML_Operations] WITH (STATE = OFF);
DROP DATABASE AUDIT SPECIFICATION [Audit_DDLDML_Operations];
ALTER SERVER AUDIT Audit_DDLDML_Ops WITH (STATE = OFF);
DROP SERVER AUDIT Audit_DDLDML_Ops;
Best Practices
- Store audit files on a secure, dedicated drive.
- Regularly archive audit logs.
- Use SQL Agent jobs for automated reporting.
- Monitor disk space to avoid failures.
