Find queries executed by an user in sql server

Question

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?

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
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