Why SQL Server Auditing Matters More Than Ever

SQL Server auditing lets you track and record activity across your SQL Server instance, capturing everything from failed logins to schema changes to data access on specific tables. You configure it by creating a Server Audit object (which defines where logs go), then attaching either a Server Audit Specification for instance-level events or a Database Audit Specification for object-level events, then enabling both.

With data privacy regulations like the Australian Privacy Act, GDPR, and PCI DSS putting real pressure on organisations to demonstrate accountability, SQL Server auditing has gone from a rarely-touched feature to a compliance requirement. The good news is that SQL Server has had solid, built-in auditing capabilities since SQL Server 2008. You don't need third-party tools to get meaningful coverage.

This article walks through configuring SQL Server auditing at both the server level and the database object level, including T-SQL scripts you can adapt for your environment.

Step 1: Create the Server Audit Object

The Server Audit object defines where your audit data goes. You have two practical options: write to a file on disk, or write to the Windows Application or Security event log.

For most production environments, writing to a dedicated file path on a separate drive is the better choice. It keeps audit data isolated, makes it easier to manage retention, and avoids cluttering the Windows event logs. If you're writing to the Windows logs, increase the maximum log file size first, otherwise you may only retain an hour or two of audit history under heavy load.

USE [master]
GO

-- Option 1: Write audit data to the Application log
CREATE SERVER AUDIT [SQL_Server_Audit]
TO APPLICATION_LOG
WITH
(
    QUEUE_DELAY = 1000
   ,ON_FAILURE  = CONTINUE
)
GO

-- Option 2: Write audit data to a file (recommended for production)
CREATE SERVER AUDIT [SQL_Server_Audit]
TO FILE ( FILEPATH = 'B:\SQLAudit' )
WITH
(
    QUEUE_DELAY = 1000
   ,ON_FAILURE  = CONTINUE
)
GO

-- Enable the audit
ALTER SERVER AUDIT [SQL_Server_Audit] WITH (STATE = ON)
GO

A few things worth noting here. The QUEUE_DELAY value (in milliseconds) controls how long SQL Server can buffer audit records before writing them. Setting it to 0 forces synchronous writes, which guarantees no records are lost but adds latency to every audited action. A value of 1000ms is a reasonable starting point for most environments.

The ON_FAILURE = CONTINUE setting tells SQL Server to keep running if it can't write audit records, rather than shutting down the instance. For strict compliance requirements, you may want ON_FAILURE = SHUTDOWN, but test that carefully in a non-production environment first. An unexpected audit write failure could take down your instance.

Step 2: Create the Server Audit Specification

The Server Audit Specification defines what server-level events get captured. This is where you specify the action groups you care about.

The example below covers the events most relevant to security and compliance auditing. Adjust the list to match your organisation's requirements and risk profile.

CREATE SERVER AUDIT SPECIFICATION [Server_Audit_Specification]
FOR SERVER AUDIT [SQL_Server_Audit]
    ADD ( AUDIT_CHANGE_GROUP )
   ,ADD ( BACKUP_RESTORE_GROUP )
   ,ADD ( DATABASE_CHANGE_GROUP )
   ,ADD ( DATABASE_OWNERSHIP_CHANGE_GROUP )
   ,ADD ( BROKER_LOGIN_GROUP )
   ,ADD ( DBCC_GROUP )
   ,ADD ( LOGIN_CHANGE_PASSWORD_GROUP )
   ,ADD ( APPLICATION_ROLE_CHANGE_PASSWORD_GROUP )
   ,ADD ( SERVER_PRINCIPAL_CHANGE_GROUP )
   ,ADD ( DATABASE_PRINCIPAL_CHANGE_GROUP )
   ,ADD ( DATABASE_PRINCIPAL_IMPERSONATION_GROUP )
   ,ADD ( DATABASE_PERMISSION_CHANGE_GROUP )
   ,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )
   ,ADD ( FAILED_LOGIN_GROUP )
   ,ADD ( SUCCESSFUL_LOGIN_GROUP )
WITH ( STATE = ON )
GO

What Do These Action Groups Actually Capture?

Here's a plain-English summary of the key groups included above:

  • AUDIT_CHANGE_GROUP - Fires whenever an audit or audit specification is created, modified, or deleted. Useful for detecting tampering with the audit configuration itself.
  • BACKUP_RESTORE_GROUP - Captures every backup and restore command. Critical for detecting unauthorised data exports.
  • DATABASE_CHANGE_GROUP - Records database creation, alteration, and deletion.
  • DATABASE_OWNERSHIP_CHANGE_GROUP - Tracks changes to database ownership.
  • BROKER_LOGIN_GROUP - Logs Service Broker transport security events.
  • DBCC_GROUP - Captures DBCC command execution, including DBCC CHECKDB and other maintenance commands.
  • LOGIN_CHANGE_PASSWORD_GROUP - Records SQL login password changes.
  • APPLICATION_ROLE_CHANGE_PASSWORD_GROUP - Fires when an application role password is changed.
  • SERVER_PRINCIPAL_CHANGE_GROUP - Tracks creation, modification, and deletion of server-level principals (logins).
  • DATABASE_PRINCIPAL_CHANGE_GROUP - Same as above but at the database level (users, roles).
  • DATABASE_PRINCIPAL_IMPERSONATION_GROUP - Captures EXECUTE AS and REVERT operations at the database level.
  • DATABASE_PERMISSION_CHANGE_GROUP - Records GRANT, DENY, and REVOKE statements on database objects.
  • DATABASE_ROLE_MEMBER_CHANGE_GROUP - Tracks when logins are added to or removed from database roles.
  • FAILED_LOGIN_GROUP - Logs every failed login attempt. One of the most important groups for detecting brute-force attacks.
  • SUCCESSFUL_LOGIN_GROUP - Logs every successful login. Can generate significant volume on busy instances, so factor that into your storage planning.

Step 3: Add Database-Level Auditing for Sensitive Tables

Server-level audit specifications capture instance-wide events, but they don't track data access on specific tables. For that, you need a Database Audit Specification.

This is particularly relevant if you need to demonstrate that access to sensitive data, such as personal information, financial records, or health data, is being monitored. The example below audits SELECT, INSERT, UPDATE, and DELETE operations on a specific table.

USE [YourDatabaseName]
GO

CREATE DATABASE AUDIT SPECIFICATION [Database_Audit_Specification]
FOR SERVER AUDIT [SQL_Server_Audit]
    ADD ( SELECT, INSERT, UPDATE, DELETE
          ON OBJECT::[dbo].[YourSensitiveTable]
          BY [public] )
WITH ( STATE = ON )
GO

Replace [YourDatabaseName] and [dbo].[YourSensitiveTable] with your actual database and table names. The BY [public] clause means the audit applies to all users. You can narrow this to specific users or roles if needed.

Be aware that auditing SELECT statements on high-traffic tables will generate a lot of audit records. Test in a non-production environment first and make sure your audit file storage can handle the volume. Running out of disk space on your audit drive is a real operational risk.

How Do You Read the Audit Data?

Once auditing is running, you can query the audit files directly using the sys.fn_get_audit_file function.

SELECT
    event_time
   ,action_id
   ,server_principal_name
   ,database_name
   ,object_name
   ,statement
   ,succeeded
   ,client_ip
FROM sys.fn_get_audit_file('B:\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC

This returns a row for each audit event, including who did what, when, from which client IP, and whether the action succeeded. The statement column captures the actual T-SQL that was executed, which is invaluable during incident investigations.

What Should You Watch Out For?

A few operational realities to keep in mind when implementing SQL Server auditing:

Storage growth. Audit files can grow quickly, especially if you're auditing successful logins or SELECT statements on busy tables. Set a maximum file size and retention policy when you create the audit, or manage rotation externally.

Performance impact. The overhead of SQL Server auditing is generally low for server-level events, but auditing high-frequency DML operations (INSERT, UPDATE, DELETE, SELECT) on large tables can add measurable overhead. Benchmark before enabling in production.

Audit tampering. Anyone with ALTER ANY SERVER AUDIT permission can disable or modify your audit configuration. The AUDIT_CHANGE_GROUP helps detect this, but consider who holds that permission carefully.

Modifying an existing audit. If you need to change audit settings after the fact, you'll need to stop the audit, make the change, then restart it. That creates a brief gap in audit coverage, so plan changes during low-activity windows.

Key Takeaways

  • SQL Server auditing is a two-part configuration: a Server Audit object (defines the destination) and an Audit Specification (defines the events). Both must be enabled for auditing to work.
  • Write audit files to a dedicated disk path rather than the Windows event logs for better control over retention and volume management.
  • Server Audit Specifications capture instance-level events like logins and permission changes. Database Audit Specifications capture object-level events like SELECT and DML on specific tables.
  • The ON_FAILURE setting is a critical decision. CONTINUE keeps the instance running if audit writes fail; SHUTDOWN guarantees no unaudited activity but carries operational risk.
  • Auditing SELECT statements on high-traffic tables generates significant volume. Test storage capacity and performance impact before enabling in production.

If you're implementing SQL Server auditing to meet compliance requirements or simply want to verify your current audit configuration is complete and correctly configured, DBA Services can help. Our SQL Server Health Check reviews your security posture, audit coverage, and configuration against best practice, and gives you a clear picture of any gaps. Get in touch with the team to find out more.