SQL Server Audit: Tracking Who Read or Changed Sensitive Tables

Some tables need a record of access as well as a record of their current contents. SQL Server Audit can capture the relevant read and change actions when the target, principals, and failure policy are defined deliberately.

Define What SQL Server Audit Must Prove

Decide which table, actions, and principals belong in the audit. A table-level read audit differs from tracking every row's prior value or proving that a transaction committed. Keep those evidence requirements explicit so the configured events are not later presented as a stronger guarantee than they provide.

I identify the required reviewer and retention period before choosing a target directory. Audit records can include statements and sensitive identifiers, so the collection needs its own access controls. A well-audited table beside an unrestricted audit folder has created a second place to inspect confidential information.

The examples use a provisioned scratch database named AuditLab and a synthetic table. Create the local C:\SqlAudit folder through the approved Windows process and give the SQL Server service identity the required write access. Restrict file readers and verify capacity before enabling the audit.

Create the Lab Object and Restricted Reader

The test table contains deliberately simple values. A user without a login supports an EXECUTE AS rehearsal inside the database; it is not a production application-account design. Give that user only the read permission needed for the access test and keep administrative setup separate from its reader context.

USE AuditLab;
GO
CREATE TABLE dbo.SensitiveAccount
(
    AccountID int NOT NULL PRIMARY KEY,
    Amount decimal(12,2) NOT NULL
);
INSERT dbo.SensitiveAccount VALUES (1,10.00);
CREATE USER AuditReader WITHOUT LOGIN;
GRANT SELECT ON dbo.SensitiveAccount TO AuditReader;

Review the actual application identity separately. A shared application login can identify the connecting service without identifying the individual person behind each request. Preserve a trustworthy application-user correlation through the accepted design when that distinction is part of the audit requirement. An identity label is evidence only within its authentication contract.

Create a SQL Server Audit File Target With a Failure Policy

A server audit defines the destination. This example chooses a bounded file collection, synchronous delivery, and FAIL_OPERATION when the target cannot record an audited action. Those are explicit lab-policy choices, not a recommendation to change every production audit to the same settings. Creating it needs the ALTER ANY SERVER AUDIT permission and changes server-level state, so use a lab instance you are allowed to configure.

USE master;
GO
CREATE SERVER AUDIT SensitiveTableAudit
TO FILE
(
    FILEPATH=N'C:\SqlAudit\',
    MAXSIZE=100MB,
    MAX_ROLLOVER_FILES=20,
    RESERVE_DISK_SPACE=OFF
)
WITH (QUEUE_DELAY=0,ON_FAILURE=FAIL_OPERATION);

Synchronous delivery adds work to the request path, so measure the accepted workload. A queued configuration has a different delivery boundary and failure exposure. Choose that tradeoff with the audit owner and availability owner together, then document the scope of the guarantee rather than assuming one configuration serves both objectives without cost.

Use SQL Server Audit with a valid destination and supported permissions on the target edition and version. A created definition starts disabled. File creation and event collection must be verified after activation; the definition alone does not establish that the service can actually write to the folder.

Activate the Table Specification and Target

The database specification selects the object actions and principals that feed the server audit. Using the public database role covers the selected actions by the database's users. A narrower principal scope needs its own accepted coverage review, including impersonation and application ownership chains.

USE AuditLab;
GO
CREATE DATABASE AUDIT SPECIFICATION SensitiveTableSpecification
FOR SERVER AUDIT SensitiveTableAudit
ADD (SELECT ON OBJECT::dbo.SensitiveAccount BY public),
ADD (INSERT ON OBJECT::dbo.SensitiveAccount BY public),
ADD (UPDATE ON OBJECT::dbo.SensitiveAccount BY public),
ADD (DELETE ON OBJECT::dbo.SensitiveAccount BY public)
WITH (STATE=OFF);
ALTER SERVER AUDIT SensitiveTableAudit WITH (STATE=ON);
ALTER DATABASE AUDIT SPECIFICATION SensitiveTableSpecification WITH (STATE=ON);

Enable both components and inspect their states. Keep audit-definition changes themselves within the organization's administrative monitoring policy. Someone with sufficient administrative control can change or disable collection, so ordinary table-event coverage should not be advertised as independent protection against every privileged action.

Generate Controlled Read and Change Events

Exercise the reader context and the four table actions on synthetic rows. Return from impersonation before administrative inspection. The sample inserts and removes a second lab row while leaving the first row available for further verification. No real sensitive data is needed to test the route.

EXECUTE AS USER=N'AuditReader';
SELECT AccountID,Amount FROM dbo.SensitiveAccount;
REVERT;
INSERT dbo.SensitiveAccount VALUES (2,20.00);
UPDATE dbo.SensitiveAccount SET Amount=21.00 WHERE AccountID=2;
DELETE dbo.SensitiveAccount WHERE AccountID=2;
SELECT name,status_desc,audit_file_path
FROM sys.dm_server_audit_status
WHERE name=N'SensitiveTableAudit';

I verify file creation and representative event records after the test. Do not intentionally remove write permissions from a production audit folder to test failure handling. Rehearse target failure in an isolated environment where the accepted service interruption and recovery route are already defined.

Read SQL Server Audit Files With UTC and Principal Filters

The file-reading function returns recorded UTC event time and identity fields. SQL Server 2022 and later requires VIEW SERVER SECURITY AUDIT for this file-inspection route; earlier supported versions use the documented broader permission. Give audit reviewers the accepted inspection access rather than unrestricted administration by default.

DECLARE @StartUTC datetime2='2026-09-20T00:00:00';
DECLARE @EndUTC datetime2='2026-09-21T00:00:00';
SELECT event_time,action_id,succeeded,session_server_principal_name,
       server_principal_name,database_principal_name,
       database_name,schema_name,object_name,statement
FROM sys.fn_get_audit_file(N'C:\SqlAudit\SensitiveTableAudit_*.sqlaudit',DEFAULT,DEFAULT)
WHERE event_time>=@StartUTC AND event_time<@EndUTC
  AND database_name=N'AuditLab'
  AND database_principal_name=N'AuditReader'
ORDER BY event_time;

If the folder holds no audit files yet, the function stops with error 33224 rather than returning an empty result. Replace the sample interval with the actual test interval, then remove or change the user filter to review the administrative writes. Interpret succeeded carefully: for these non-login events it describes the permission check, not proof of final transaction commit. The statement field also does not provide complete before-and-after row images.

Plan File Retention and Target Failure

Rollover limits bound the local file population and can remove older files. They do not promise a particular number of retention days, because event volume determines how quickly files fill. Archive accepted records before rollover and verify that the archive can be read with its required metadata and permissions.

CONTINUE prioritizes ongoing operations while audit writes are unavailable and therefore permits an evidence gap. FAIL_OPERATION rejects actions that require the unavailable audit route. SHUTDOWN can stop the instance and requires an explicitly accepted availability policy and permissions. Dedicated administrator access and other documented exceptions also belong in the policy's interpretation.

Which outcome is acceptable when the destination is full at midnight? Answer that before production activation. Monitor target capacity, runtime status, collection gaps, and archival completion. A failure policy is useful only when its operational consequence has been rehearsed and someone is responsible for restoring the evidence route.

Verify Coverage as the Application Changes

Test direct access, approved module access, impersonation, failed permission checks, and the actual application path. Confirm the required identities and object actions appear under those paths. Review renamed or replaced objects and changed application accounts so the specification continues to describe the intended table.

SQL Server Audit provides valuable access evidence when the record meaning and collection boundaries are understood. Keep its retention, reviewer permissions, and failure response with the table's operational documentation. The useful result is inspectable coverage for the stated requirement, rather than an enabled audit whose guarantees nobody has defined.

Related reading on this blog: Script to Audit Login and Role Member Change and SQL Audit Date Time Does Not Match Machine Date Time: Solution.

An audit record is not a complete data-history guarantee, it is evidence of selected actions within a defined collection policy.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.