How to Create a SQL Server Audit for DML and DDL Operations (Step-by-Step Guide)

 

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_file reads 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.