how to get History of queries executed with username in SQL

Question

I can get executed queries by using:

SELECT deqs.last_execution_time AS [Time]
    ,dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

but I am also looking for username column, who executed these queries.

score 33 · Accepted Answer · edited Jun 13 '20 at 23:00

I would suggest you to create a server side trace or enable SQL Audit to track down activity from users that you don't trust.

Remember that DMV data gets reset if the DMV is cleared out, SQL Server is restarted, etc.

The closest you can get is using below query:

/******************************************************
Script : Findout Who did what ?
Author : Kin Shah .. written for dba.stackexchange.com
*******************************************************/
USE master
go
SELECT sdest.DatabaseName 
    ,sdes.session_id
    ,sdes.[host_name]
    ,sdes.[program_name]
    ,sdes.client_interface_name
    ,sdes.login_name
    ,sdes.login_time
    ,sdes.nt_domain
    ,sdes.nt_user_name
    ,sdec.client_net_address
    ,sdec.local_net_address
    ,sdest.ObjName
    ,sdest.Query
FROM sys.dm_exec_sessions AS sdes
INNER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id
CROSS APPLY (
    SELECT db_name(dbid) AS DatabaseName
        ,object_id(objectid) AS ObjName
        ,ISNULL((
                SELECT TEXT AS [processing-instruction(definition)]
                FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
                FOR XML PATH('')
                    ,TYPE
                ), '') AS Query

    FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
    ) sdest
where sdes.session_id <> @@SPID 
--and sdes.nt_user_name = '' -- Put the username here !
ORDER BY sdec.session_id

score 0 · Answer 2 · edited Feb 15 '23 at 16:21

Extended Kin's query to retrieve the latest / most recent history, therefore included the "Last_execution_time" from "dm_exec_query_stats" DMV:

SELECT top 100 sdest.DatabaseName 
    ,sdes.session_id
    ,qs.last_execution_time
    ,sdes.[host_name]
    ,sdes.[program_name]
    ,sdes.client_interface_name
    ,sdes.login_name
    ,sdes.login_time
    ,sdes.nt_domain
    ,sdes.nt_user_name
    ,sdec.client_net_address
    ,sdec.local_net_address
    ,sdest.ObjName
    ,sdest.Query
FROM sys.dm_exec_sessions AS sdes
    INNER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id
    CROSS APPLY (SELECT db_name(dbid) AS DatabaseName
            ,object_id(objectid) AS ObjName
            ,ISNULL((
                    SELECT TEXT AS [processing-instruction(definition)]
                    FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
                    FOR XML PATH('')
                        ,TYPE
                    ), '') AS Query
        FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
        ) sdest
    CROSS APPLY sys.dm_exec_query_stats qs
where sdes.session_id <> @@SPID 
    and sdes.nt_user_name like '%Your_User_Name%'   --UserName
ORDER BY qs.last_execution_time DESC

how to get History of queries executed with username in SQL

2 Answers2

Linked