3

I read in a stackoverflow post that you can restore scripts from the past 24 hours by using this query:

Use <database>
SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC

But I want to see only queries which a specific user ran, how can I perform this?

1
  • 2
    That query shows you the text of queries that happen to be in the plan cache. It is absolutely not an exhaustive list of all SQL that was executed in the past 24 hours. Feb 21, 2014 at 14:16

2 Answers 2

4

In order to achieve it, you'll need to capture this information at run time using DMV's or Extended Events.

2

Below query will help you :

SELECT cr.DatabaseName
    ,s.session_id
    ,s.host_name
    ,s.program_name
    ,s.client_interface_name
    ,s.login_name
    ,s.login_time
    ,s.nt_domain
    ,s.nt_user_name
    ,c.client_net_address
    ,c.local_net_address
    ,cr.ObjName
    ,cr.Query
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS c ON c.session_id = s.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(c.most_recent_sql_handle)
                FOR XML PATH('')
                    ,TYPE
                ), '') AS Query

    FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)
    ) cr
where s.nt_user_name = '' -- filter here your user name
and s.session_id <> @@SPID
ORDER BY c.session_id
2
  • 2
    This query would work alright if the connection still exists. If it was closed, not so much, unfortunately.
    – Marian
    Feb 21, 2014 at 14:15
  • 1
    @Marian Agreed. The other way is ONLY auditing through profiler (server side trace) or XEvents.
    – Kin Shah
    Feb 21, 2014 at 14:23

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.