---

Methodology for Correlating User Accounts with Executed SQL Queries in Microsoft SQL Server Logs

When conducting an investigation to align a user account with a specific SQL query observed in the logs, the approach should be structured, methodical, and reliant on available historical data. Given that this is not an active security incident but rather an investigative analysis of prior events, the methodology should focus on extracting and correlating previously logged data. The following techniques outline the most effective strategies for achieving this objective.

---

1. Query SQL Server Logs to Identify User Activity

SQL Server maintains logs that capture user authentication events and, if configured, executed queries. These logs provide foundational data for associating an SQL query with a specific user account.

Extracting User Login Events from the SQL Server Error Log

The error log retains authentication events, which can help establish whether a particular user was active within the database at the time the query in question was executed. The following command retrieves login events:

EXEC sp_readerrorlog 0, 1, 'Login'; 

This will return a list of login records, including timestamps, which can be used to establish the presence of a user within the system.

Extracting Executed Queries from SQL Server Logs

If SQL Server auditing or logging of executed queries was enabled, a search within the logs can reveal evidence of SQL statement execution:

EXEC sp_readerrorlog 0, 1, 'SELECT'; 

This command will return any recorded queries containing the keyword SELECT, though modifications may be required to locate other types of SQL commands.

This approach is particularly useful if SQL Server Audit or C2 Audit Mode was enabled at the time of execution. If these were not configured in advance, log data related to executed queries may be incomplete.

---

2. Leverage the SQL Server Default Trace for Query-User Correlation

SQL Server includes a default trace, which, if enabled, logs user activity, including executed queries and system changes. This trace can be instrumental in reconstructing historical user-query associations.

Verifying Whether Default Trace is Enabled

Before proceeding, it is necessary to confirm that default trace functionality is active:

SELECT * FROM sys.configurations WHERE name = 'default trace enabled'; 

If the output indicates that the setting is enabled (value = 1), the following query can be used to extract relevant user-query mappings:

SELECT  

    tr.StartTime, 

    tr.LoginName, 

    tr.ApplicationName, 

    tr.HostName, 

    tr.TextData 

FROM sys.fn_trace_gettable( 

    (SELECT path FROM sys.traces WHERE is_default = 1), DEFAULT 

) tr 

WHERE tr.TextData IS NOT NULL 

ORDER BY tr.StartTime DESC; 

This will provide a structured view of executed queries, their associated timestamps, and the credentials of the user responsible for their execution.

This method is particularly effective for investigations where default tracing was not manually disabled by administrators.

---

3. Extract Query Execution Information from Dynamic Management Views (DMVs)

If the query in question was executed recently and remains in SQL Server’s cache, Dynamic Management Views (DMVs) can be utilized to retrieve session details and map SQL activity to specific users.

Querying DMVs for Active or Cached SQL Queries

The following query extracts active sessions, mapping SQL execution details to the corresponding user account:

SELECT 

    s.session_id, 

    s.login_name AS executing_user, 

    s.host_name, 

    s.program_name, 

    r.start_time, 

    r.status, 

    r.command, 

    q.text AS sql_text 

FROM sys.dm_exec_requests r 

JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id 

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) q 

ORDER BY r.start_time DESC; 

This will return a dataset containing:

• The user who executed the query

• The associated host from which the query was issued

• The SQL command executed

• The session start time

While this method is effective, its limitation lies in the volatility of cached data; once a query is flushed from memory, it will no longer be retrievable through DMVs.

---

4. Utilize Extended Events to Review Historical Query Execution

If SQL Server’s Extended Events feature was configured prior to the time of interest, it can serve as a retrospective tool for correlating SQL execution with specific user accounts.

Querying Extended Events Log Files

The following query extracts recorded query executions along with user details from an Extended Events session:

SELECT 

    event_data.value('(event/@timestamp)[1]', 'datetime') AS event_time, 

    event_data.value('(event/action[@name="username"]/value)[1]', 'nvarchar(128)') AS executing_user, 

    event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(128)') AS client_host, 

    event_data.value('(event/data[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text 

FROM ( 

    SELECT CAST(event_data AS XML) AS event_data 

    FROM sys.fn_xe_file_target_read_file('C:\XE\*.xel', NULL, NULL, NULL) 

) AS XEvents; 

This approach is particularly useful in post-incident investigations where Extended Events had been previously enabled and configured to log relevant database activity.

---

5. Correlate SQL Server Logins with Windows Security Event Logs

In cases where SQL Server logs alone are insufficient, Windows Security Event Logs may provide additional correlation points, particularly for tracking user authentication events.

Extracting SQL Server Login Events from Windows Logs

Using PowerShell, the following command searches for SQL Server login events within the Windows Security log:

Get-WinEvent -LogName "Security" | Where-Object { $_.Id -eq 4624 -and $_.Message -match "MSSQL" } 

This method can be particularly useful when attempting to correlate SQL authentication activity with executed queries, particularly if SQL Server logs have been purged or overwritten.

---

Final Considerations for Investigative Analysis

The approach taken should be guided by the availability of log data, the time sensitivity of the query execution, and the depth of historical records maintained by the SQL Server environment. Below is a prioritized decision matrix for selecting the appropriate method:

1. SQL Server Logs (sp_readerrorlog) – Suitable when SQL logging was configured and executed queries are stored.

2. Default Trace (sys.fn_trace_gettable) – Useful if default trace logging was active at the time of query execution.

3. Dynamic Management Views (DMVs) – Effective for recent queries still in memory.

4. Extended Events (sys.fn_xe_file_target_read_file) – Applicable if historical logging was configured.

5. Windows Security Logs (Get-WinEvent) – Supports authentication tracking when SQL logs are incomplete.

Investigators should begin by assessing the availability of logging configurations and prioritizing methods based on log retention policies. If audit mechanisms were not proactively enabled before the period of interest, retrieving user-query correlations may be limited to session data currently residing in memory.

For more structured forensic analysis, implementing SQL Server Audit and Extended Events logging as a proactive measure is strongly recommended. If persistent audit requirements exist, enabling Transparent Data Encryption (TDE) and full compliance logging may further enhance investigative capabilities.