Auditing Who Changed What, and When

SQL Server auditing needs a clear question before it needs a destination file. Recording who issued a statement is different from retaining every old and new row value.

Separate Identity From Data History

Someone asks who changed an amount yesterday. To answer, you may need an authenticated identity, a timestamp, the operation, and the affected values. Those details do not automatically appear together in one built-in feature.

A shared application login identifies the application connection, not necessarily the human behind it. If individual attribution matters, design a trustworthy application identity trail too. A username supplied freely by a caller is not strong evidence on its own.

Decide how long evidence must remain available and who can read or change it. That requirement affects storage, access, and operational monitoring. Retention is part of the design, not a cleanup detail added later.

Use SQL Server Audit for Selected Events

SQL Server Audit records configured server-level and database-level events. You choose the actions or action groups and the target. A defined audit must also be enabled and functioning before it can capture relevant activity.

SELECT name, type_desc, is_state_enabled,
       queue_delay, on_failure_desc
FROM sys.server_audits;
SELECT name, is_state_enabled, audit_guid
FROM sys.server_audit_specifications;
SELECT name, is_state_enabled, audit_guid
FROM sys.database_audit_specifications;

Run the database specification query in the database you are reviewing. Then inspect the specification details to establish which actions are actually selected. The existence of an audit name does not prove coverage of the operation in question.

SELECT database_specification_id, audit_action_name,
       class_desc, major_id, minor_id,
       audited_principal_id, audit_result
FROM sys.database_audit_specification_details;

Audit output can include statement and principal context for selected events. It is not a general before-and-after row archive. Test the exact event and inspect the resulting record before promising that it answers an investigation question.

Treat Default Trace as Limited Historical Help

The default trace can contain useful configuration and object-change events when enabled. Its files roll over, so older evidence may already be gone. It does not provide a complete history of every row modification.

SELECT id, status, path, start_time,
       max_size, max_files, is_rollover
FROM sys.traces
WHERE is_default = 1;

This identifies the trace and its storage settings without claiming that a particular event remains available. SQL Trace is deprecated, so do not design a new long-term audit around it. Use a supported purpose-built audit or Extended Events design for the required evidence.

If investigating an existing incident, preserve relevant available records through the approved process. Record the collection time and source. Do not mistake a missing trace event for proof that no change occurred.

Use Change Tracking for Synchronization

Change Tracking identifies rows that changed since a tracked version. It is designed to help consumers find current changes efficiently. It does not retain every intermediate value or identify the person responsible.

SELECT DB_NAME(database_id) AS database_name,
       is_auto_cleanup_on, retention_period,
       retention_period_units_desc
FROM sys.change_tracking_databases;
SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       OBJECT_NAME(object_id) AS table_name,
       is_track_columns_updated_on
FROM sys.change_tracking_tables;

A consumer must stay within the valid retention window and use the documented version checks. If its saved version becomes too old, reinitialization may be necessary. Keeping a watermark indefinitely does not preserve the missing change history.

Use CDC When Changed Values Matter

Change Data Capture reads logged changes asynchronously and exposes captured row data through change tables and functions. It can retain before and after values for updates within its retained interval. It is commonly useful for downstream data movement.

SELECT name, is_cdc_enabled
FROM sys.databases
WHERE database_id = DB_ID();
SELECT SCHEMA_NAME(schema_id) AS schema_name, name
FROM sys.tables
WHERE is_tracked_by_cdc = 1;

CDC is not automatically a reliable record of the individual application user. Capture delay, cleanup, and access to sensitive historical values also need attention. In SQL Server, capture and cleanup operations require the appropriate operational support, including Agent jobs in ordinary standalone setups.

Measure the workload and storage effects of the chosen configuration. More captured data and longer retention create ongoing costs. Avoid a universal overhead percentage that ignores transaction volume and consumer behavior.

Test the Evidence Before You Need It

Create a controlled change through the real application path and inspect what each configured mechanism records. Verify identity, timestamps, values, and retention separately. Document any part of the original question that remains unanswered.

Monitor collection failures and protect the evidence destination from casual alteration. Review access and retention with the data owner. An audit that nobody checks can quietly become an empty promise.

An audit trail is not a feature name, it is evidence that answers a defined question.

This post was rewritten from scratch in September 2026. The original, published on 2009-07-08, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

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