I want to find all the queries executed by an particular user in a database over a period of time.

I learnt about trace and XEvents, but these are not setup yet, Will i be still able to get the queries executed by an particular user?

  • 1
    The short answer is no. Consider the storage and overhead of tracing indiscriminately would be considerable with many users and queries.
    – Dan Guzman
    Mar 11, 2019 at 10:51
  • What edition and version you are using? Jul 2, 2019 at 3:26

1 Answer 1


You would need to start logging account activity of accounts that you don't trust to a table for a complete list. All of the other options (like querying dmvs) will give you temporal data and there's no guarantee that it is complete.

Here is an example of what you are looking for but consider creating a job or something that logs these results to a table that only you have access to view or even see. You could put a filter that only stores accounts that you are wanting to monitor(check out the second where clause that's commented out) if storage is an issue.

This queries session info, although you might want to only select the columns that you are interested in like Database Name, login_name, Loginin_Time, and Query.

Script : Findout Who did what ?
Author : Kin Shah .. written for dba.stackexchange.com

USE master
SELECT sdest.DatabaseName 
FROM sys.dm_exec_sessions AS sdes
INNER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id
    SELECT db_name(dbid) AS DatabaseName
        ,object_id(objectid) AS ObjName
                SELECT TEXT AS [processing-instruction(definition)]
                FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
                FOR XML PATH('')
                ), '') 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.