Auditing SELECT Statements on Sensitive Tables

A sensitive table can be read without changing a single row. Auditing SELECT statements on sensitive tables gives you evidence of access, but the audit needs a narrow scope and a review plan.

Define the Access Question for Auditing SELECT Statements

Decide which table, principal, and time period the audit must cover. A broad audit of every SELECT on a busy database can create heavy volume and hard-to-review output. A narrow specification for sensitive objects is easier to retain and interpret. Include the data owner and security team in the scope decision.

I ask what action the reviewer would take after seeing an event. If every normal report query produces an alert, the feed will be ignored. Define expected readers and suspicious patterns. The audit should support an investigation, not create a second database of noise. Which table would you want an access record for tomorrow? Start there.

Build the Server Audit Target

SQL Server Audit uses a server audit object to define a target such as an audit file. Place the file on protected storage with capacity monitoring and restricted access. Review rollover and failure behavior for your compliance requirement. An audit that fills a volume or drops events silently defeats its purpose.

The example creates a file target at a placeholder Windows folder. Create the folder and service account permissions first. Use a reviewed path and retention policy. In a production deployment, decide how failures should affect operations before enabling the audit.

USE master;
CREATE SERVER AUDIT [SensitiveReadAudit]
TO FILE (FILEPATH = 'D:\SQLAudit\');
ALTER SERVER AUDIT [SensitiveReadAudit]
WITH (STATE = ON);

Specify SELECT on the Table

A database audit specification links the action to the server audit. The example captures SELECT on one object by public, which covers users through that principal in the intended scope. Review the exact principal choice and test with real application accounts. The audit records access attempts according to the specified action group or object action, not the rows returned.

I test a known authorized SELECT and confirm an event appears. Then I test a role that should be denied and inspect the record. Do not assume the specification works because CREATE completed. The target and database specification must both be enabled.

USE [YourDatabase];
CREATE DATABASE AUDIT SPECIFICATION [SensitiveTableSelectAudit]
FOR SERVER AUDIT [SensitiveReadAudit]
ADD (SELECT ON OBJECT::[dbo].[SensitiveTable] BY [public])
WITH (STATE = ON);

Read the Audit File

sys.fn_get_audit_file reads audit file records. Restrict access to the files and to the query results because they can reveal user and object details. Filter by time and object when investigating. The file pattern must match the actual audit target path. Rollover files are part of the record, so plan retention before old files are removed.

The query below uses a placeholder path and returns recent records. Check event_time, server principal, database principal, object name, action, and success. A success flag describes the audited action outcome; it does not tell you how many rows a user saw.

SELECT TOP (100)
       event_time,
       server_principal_name,
       database_principal_name,
       database_name,
       object_name,
       action_id,
       succeeded,
       statement
FROM sys.fn_get_audit_file('D:\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;

Understand Event Volume When Auditing SELECT Statements

A reporting job can issue many SELECT statements against the same table. Audit volume depends on workload, query pattern, and scope. Test under representative traffic and measure actual file growth. Do not copy a retention size from another server. If the target volume fills, the chosen audit failure behavior determines the operational consequence.

I review file size during the pilot and adjust the scope before broad rollout. A flood of expected reads hides the rare access you care about. Narrow by object or principal when the audit question allows it. Keep a clear record of excluded access so nobody mistakes a scoped audit for universal coverage.

Distinguish Login From Human

The event records the SQL principal and related context. An application using one shared login can make many human actions look identical to SQL Server. Correlate with application request logs and user identity where the investigation needs person-level attribution. Do not claim the audit identifies an end user when the database only saw a service account.

I ask whether the application carries a trusted user identifier into its own logs. The SQL audit and application trace can then form a timeline. Client-supplied application names are useful context but not a security identity. State exactly what each record proves. That honesty makes the evidence stronger.

Protect Audit Integrity

Restrict who can disable the audit, change its specification, or read and remove files. Monitor the audit object’s state and audit target health. Send protected copies to a separate retention system when policy requires it. Keep clocks synchronized so records can be correlated with application and infrastructure logs.

I include audit configuration in permission reviews. A privileged login that can alter the audit is part of the threat model. No database feature can make a sysadmin-level attacker powerless by itself. The design should make unauthorized changes visible through independent controls where required.

Review Findings From Auditing SELECT Statements on a Schedule

Name the reviewer and set a cadence. Define expected service accounts and maintenance windows, then look for new principals, unusual times, and failed access. An audit file stored and never opened provides little operational value. Keep a procedure for investigating a suspicious event without spreading sensitive query text broadly.

I send a short exception report rather than a raw file dump. The reviewer can then inspect the original protected record when needed. Document the decision and escalation. A useful audit closes the loop between capture and human action. Otherwise it is just another growing directory.

Test Retention and Recovery

Confirm that file rollover, backup, and retention meet the required investigation window. Restore or read an archived audit file in a safe environment to prove the process. Test what happens when the target path is unavailable. The chosen failure mode should be understood by both operations and security owners.

What evidence would remain after the server itself failed? Answer that before calling the audit complete. Auditing SELECT statements can provide valuable accountability, but only when the events survive, remain readable, and receive review. The specification is the start of that chain.

Related reading on this blog: Auditing Who Changed What, and When and Database Auditing and Compliance.

A SELECT audit is not a privacy control by itself, it is evidence that must be protected and reviewed.

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