Finding Out Who Changed That Row

Finding who changed data is much easier when the required evidence was captured before the change. The transaction log supports recovery, but it is not a ready-made report of every human action.

Preserve the Question and the Evidence

Identify the table, business key, suspected change, and time window. Record the time zone and how the discrepancy was discovered. A precise question is more useful than searching the entire server for anything unusual.

Preserve available application logs, audit files, and relevant backup information through the approved process. Avoid changing retention or cleaning files during the investigation. Keep a record of where each piece of evidence came from.

SELECT SYSUTCDATETIME() AS collected_at_utc,
       @@SERVERNAME AS server_name, DB_NAME() AS database_name;
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE database_id = DB_ID();

These settings provide context, not the identity of the person who changed a row. Keep observations separate from conclusions. A likely explanation remains a hypothesis until the evidence supports it.

Understand What the Transaction Log Is For

SQL Server records transaction information needed for recovery and related features. Log records describe engine operations, which do not map neatly to one business action. A single statement can generate many records.

Reusable log space can eventually be overwritten, and retained log backups depend on the backup strategy. The current log is not an unlimited historical archive. Availability of old evidence varies with activity and retention.

You may encounter fn_dblog in troubleshooting articles. It is undocumented and should not be treated as a stable supported audit interface. Interpreting raw log records also requires expertise and does not automatically establish a human identity.

Check Whether Audit Was Already Capturing the Action

SQL Server Audit can record selected actions with statement and principal context. First establish whether the relevant audit and specification were enabled at the time. A configuration created afterward cannot reconstruct missing events.

SELECT name, type_desc, is_state_enabled
FROM sys.server_audits;
SELECT name, is_state_enabled
FROM sys.database_audit_specifications;
SELECT audit_action_name, class_desc, major_id,
       audited_principal_id
FROM sys.database_audit_specification_details;

Read the retained audit output from the approved destination and inspect the exact event fields. The session principal and current execution principal can differ. Do not collapse those identities into one label without understanding the execution context.

For most audit events, succeeded reports the permission-check outcome rather than proving that the whole operation committed. Correlate the event with transaction and application evidence. An attempted statement is not automatically a completed business change.

Use Other History for the Questions It Can Answer

The default trace may help with selected object or configuration changes while retained files remain available. It does not capture a complete row-change history. SQL Trace is deprecated, so it is not the foundation for a new audit design.

SELECT id, path, start_time, is_rollover
FROM sys.traces
WHERE is_default = 1;
SELECT name, is_cdc_enabled
FROM sys.databases
WHERE database_id = DB_ID();

CDC can preserve captured row changes within its retention window, but it is not a complete user-attribution mechanism. Temporal history can show earlier row versions when already enabled. Neither feature automatically answers who caused the change.

Change Tracking is narrower still: it identifies changed keys for synchronization rather than preserving every old value. Choose the evidence source according to its actual contents. Similar names do not mean equivalent audit guarantees.

Distinguish the Login From the Person

A shared application account may be the only database principal visible for many users. Connection pooling makes session identity even less useful as a direct human label. Correlate with trustworthy application request and user records.

SELECT ORIGINAL_LOGIN() AS original_login,
       SUSER_SNAME() AS current_login,
       USER_NAME() AS current_database_user,
       SESSION_CONTEXT(N'UserId') AS application_user_context;

This displays the current session's context only. A session-context value is meaningful evidence only if the application sets and protects it appropriately. Do not assume an arbitrary caller-supplied value proves identity.

Review how pooled connections initialize and clear context between requests. Retain stable request identifiers across the application and database evidence. Attribution is a designed chain, not a column name.

Build the Next Investigation Before It Happens

If existing evidence cannot answer the question, report that limit plainly. Do not identify a person from a weak coincidence. Preserve what is known and involve the appropriate owners for further investigation.

Design future capture around the required actor, action, object, values, time, and retention. Test a controlled change and verify the resulting evidence. A reliable answer begins before the next unexpected update.

Change attribution is not a log-reading trick, it is evidence designed before the event.

This post was rewritten from scratch in September 2026. The original, published on 2016-02-09, 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.