0

I'm looking for a DMV query for SQL Server 2008 R2 which shows me all queries (full history) run by a specific user.

I have this code for now:

SELECT *
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

but is it possible to join another DMV which allows me to filter results on login name?

  • Does how to get History of queries executed with username in SQL help ? You wont be getting FULL history though - unless you have enabled some logging. – Kin Shah May 2 '16 at 16:09
  • "I'm looking for a DMV query for SQL Server 2008 R2 which shows me all queries (full history) run by a specific user." - This doesn't exist out the box. You would need to use tracing, extended events or SQL Audit. – Martin Smith May 2 '16 at 19:43
-1

LoginName and Hostname from Sys.sysprocesses

you've already got a cross apply to dm_exec_Sql_Text. so you've already got everything else you need.

|improve this answer|||||
  • 1
    Can you give an example of how you would join sys.sysprocesses to the DMVs used in the question? – Mark Sinkinson May 2 '16 at 16:06
  • select sp.loginname FROM sys.sysprocesses sp CROSS APPLY Sys.dm_exec_sql_text(sp.sql_handle) – Charles Hightower May 2 '16 at 16:22
  • 1
    And how does this get a full history? – Mark Sinkinson May 2 '16 at 16:26
  • DMV's won't give you a full history anyways unless you log their output longitudinally. You'd have to build a job to do that. and you'd have to have it in place prior to wanting to answer the question of What did Steve run last Tuesday? – Charles Hightower May 2 '16 at 16:48

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.