0

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 '19 at 10:51
  • What edition and version you are using? – Rajesh Ranjan Jul 2 '19 at 3:26
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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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