SQL Server Auditing

Overview

SQL Server Auditing provides a robust framework for tracking and recording events that occur on the database engine. Audits capture information about security-relevant actions, data modifications, and configuration changes, helping organizations meet compliance requirements such as GDPR, HIPAA, and PCI‑DSS.

CREATE SERVER AUDIT MyAudit
TO FILE (FILEPATH = 'C:\AuditLogs\')
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
GO

Enable Auditing

To start capturing audit data, you must create a server audit, enable it, and then define audit specifications.

Step‑by‑step

  1. Define a server audit (destination, rollover, etc.).
  2. Enable the server audit.
  3. Create database or server audit specifications to select events.
ALTER SERVER AUDIT MyAudit WITH (STATE = ON);
GO

CREATE SERVER AUDIT SPECIFICATION MyServerSpec
FOR SERVER AUDIT MyAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP);
GO

ALTER SERVER AUDIT SPECIFICATION MyServerSpec WITH (STATE = ON);
GO

Audit Action Groups

Action groups are pre‑defined collections of related events. Choose groups that align with your compliance goals.

GroupDescription
FAILED_LOGIN_GROUPFailed login attempts
SUCCESSFUL_LOGIN_GROUPSuccessful login attempts
SCHEMA_OBJECT_CHANGE_GROUPDDL changes (CREATE, ALTER, DROP)
DATABASE_PRINCIPAL_CHANGE_GROUPPermission changes
SELECT_GROUPSELECT statements (requires careful use)

Audit Specifications

Audit specifications tie a server audit to the actions you wish to capture. You can define both server‑level and database‑level specifications.

CREATE DATABASE AUDIT SPECIFICATION MyDbSpec
FOR SERVER AUDIT MyAudit
ADD (SELECT, INSERT, UPDATE, DELETE ON DATABASE::[MyDB] BY PUBLIC);
GO

ALTER DATABASE AUDIT SPECIFICATION MyDbSpec WITH (STATE = ON);
GO

Filters & Target Types

Filters refine what gets logged based on principals, object names, or T‑SQL statements.

ALTER SERVER AUDIT SPECIFICATION MyServerSpec
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WHERE (object_name = N'SensitiveTable');
GO

Supported target types include FILE, APPLICATION_LOG, and SECURITY_LOG. File targets allow for easy archiving and analysis.

Best Practices

  • Enable audits on a dedicated drive to avoid I/O contention.
  • Set QUEUE_DELAY to a low value for near‑real‑time monitoring.
  • Rotate audit files frequently to manage storage.
  • Regularly review audit logs with a SIEM or PowerShell scripts.
  • Combine audits with Logon Triggers for enhanced security.

Frequently Asked Questions

Can I audit SELECT statements?

Yes, but be aware of the performance impact. Use column‑level filters or target specific high‑risk tables.

Where are audit files stored by default?

If not specified, audit files are stored in the SQL Server default data directory.

How do I read audit files?

Use sys.fn_get_audit_file or the Microsoft Log File Viewer (SQL Server Management Studio).