Which user ran what query

Question

Using SQL Server 2008 R2 - Newbie - Is there a query that I can run against a system database to tell who executed a query against what database in a specific time period? Want to see how many times a particular user is running queries. Any help is greatly appreciated.

Answer 1

You cannot tell this after the fact. You can tell how many times a certain query has been excecuted, if the query plan is still in cache, but you cannot tell by which user and when, unless you can draw conclusions from the query text. Here is such a query:

; WITH basedata AS (
   SELECT qs.statement_start_offset/2 AS stmt_start,
          qs.statement_end_offset/2 AS stmt_end,
          est.encrypted AS isencrypted, est.text AS sqltext,
          qs.execution_count
   FROM   sys.dm_exec_query_stats qs
   CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
)
   SELECT execution_count,
          CASE WHEN isencrypted = 1 THEN '-- ENCRYPTED'
               WHEN stmt_start >= 0
               THEN substring(sqltext, stmt_start + 1,
                              CASE stmt_end
                                   WHEN 0 THEN datalength(sqltext)
                                   ELSE stmt_end - stmt_start + 1
                              END)
          END AS Statement
   FROM   basedata

Note that the queries in many cases could reside in stored procedures.

If you want to trace a certain user during a certain time, you need to set up a trace in advance.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Thank you - I appreciate your help.