Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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. –  Martin Smith Feb 21 at 14:16

2 Answers 2

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

share|improve this answer

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
share|improve this answer
2  
This query would work alright if the connection still exists. If it was closed, not so much, unfortunately. –  Marian Feb 21 at 14:15
1  
@Marian Agreed. The other way is ONLY auditing through profiler (server side trace) or XEvents. –  Kin Feb 21 at 14:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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