How to Audit Access in SQL Server

Galaxy Glossary

How do I audit data and login access in SQL Server?

Audit access tracks who reads, modifies, or deletes data and logs the activity for compliance and troubleshooting.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What does “audit access” mean in SQL Server?

Audit access records login attempts and data-level actions, writing them to a server or file target so security teams can review who touched sensitive data.

When should I enable auditing?

Enable auditing when your ecommerce app stores PII, processes payments, or must meet SOC 2/GDPR requirements. Start before going to production to capture a full history.

How do I create a server-level audit?

Run CREATE SERVER AUDIT to define the log target, then CREATE SERVER AUDIT SPECIFICATION to choose audit actions such as SUCCESSFUL_LOGIN_GROUP.

Step 1 – Create the audit

CREATE SERVER AUDIT Audit_Login_Access TO FILE (FILEPATH = 'C:\AuditLogs\') WITH (ON_FAILURE = CONTINUE);

Step 2 – Create an audit specification

CREATE SERVER AUDIT SPECIFICATION Audit_Login_Spec FOR SERVER AUDIT Audit_Login_Access ADD (SUCCESSFUL_LOGIN_GROUP), ADD (FAILED_LOGIN_GROUP);

Step 3 – Enable audit and spec

ALTER SERVER AUDIT Audit_Login_Access WITH (STATE = ON); ALTER SERVER AUDIT SPECIFICATION Audit_Login_Spec WITH (STATE = ON);

How do I audit table access in a database?

Create a DATABASE AUDIT SPECIFICATION and add action groups such as SELECT, INSERT, UPDATE, and DELETE for the Orders or Customers tables.

USE EcommerceDB; GO CREATE DATABASE AUDIT SPECIFICATION Audit_OrderTable FOR SERVER AUDIT Audit_Login_Access ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.Orders BY PUBLIC); GO ALTER DATABASE AUDIT SPECIFICATION Audit_OrderTable WITH (STATE = ON);

Where are audit logs stored?

Logs are written to the FILEPATH you specify or to the Windows Application log. Query sys.fn_get_audit_file to read them in SQL Server.

Best practices for auditing

Limit action groups to what you need, archive audit files to cheaper storage, and test performance impact in staging. Rotate files with MAXSIZE to prevent disk-full errors.

Why How to Audit Access in SQL Server is important

How to Audit Access in SQL Server Example Usage


-- Track every UPDATE on the Products table so merchandisers know who changes pricing
USE EcommerceDB;
GO
CREATE DATABASE AUDIT SPECIFICATION Audit_ProductUpdates
FOR SERVER AUDIT Audit_Login_Access
ADD (UPDATE ON dbo.Products BY PUBLIC);
GO
ALTER DATABASE AUDIT SPECIFICATION Audit_ProductUpdates WITH (STATE = ON);

How to Audit Access in SQL Server Syntax


-- Create a server audit
CREATE SERVER AUDIT audit_name
TO { FILE (FILEPATH = 'path') | APPLICATION_LOG | SECURITY_LOG }
[ WITH (ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION },
        QUEUE_DELAY = milliseconds,
        MAX_FILES = number,
        MAX_ROLLOVER_FILES = number,
        MAX_FILE_SIZE = MB ) ];

-- Create server audit specification
CREATE SERVER AUDIT SPECIFICATION spec_name
FOR SERVER AUDIT audit_name
ADD ( {action_group | {action ON object} } [, ...] );

-- Create database audit specification
CREATE DATABASE AUDIT SPECIFICATION spec_name
FOR SERVER AUDIT audit_name
ADD ( {action_group | {action ON object} } [, ...] );

-- Enable/disable
ALTER SERVER AUDIT audit_name WITH (STATE = { ON | OFF });
ALTER SERVER AUDIT SPECIFICATION spec_name WITH (STATE = { ON | OFF });

Common Mistakes

Creating an audit but forgetting to enable it (STATE = ON). The audit exists yet never captures events. Always run ALTER SERVER AUDIT ... STATE = ON after creation.
Auditing every action group on every table. This floods logs and hurts performance. Instead, audit only sensitive tables (Customers, Orders) and critical actions (UPDATE, DELETE).

Frequently Asked Questions (FAQs)

Can I store audit logs outside the server?

Yes. Point FILEPATH to a network share or write to the Windows security log for centralized collection.

Does auditing slow down queries?

Minimal overhead (1-5 %) when capturing specific actions. Wide-open audits can cause noticeable latency.

How do I read audit files?

Use sys.fn_get_audit_file('C:\AuditLogs\*', NULL, NULL) or open the .sqlaudit file in SSMS Log File Viewer.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie