Query executed by the user

[Jaison Jose]

Hi Guys,

Is it possible to find each user account who has performed query operations
against a DB?

I did manage to find something for SQL 2005 which does give me the query
that is executed and the time..This does not list the user account..

SELECT deqs.last_execution_time AS [Time], dest.text AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

I want to find these info for both SQL 2000 and 2005...

Thanks a lot for your answers,
JJ

[Erland Sommarskog]

To this sort of auditing, you need to plan ahead. In SQL 2000 and SQL 2005
SQL Trace is about your only choice.

SQL 2008 Enterprise Edition introduces a new feature, SQL Server Audit.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Uri Dimant]

Jaison
Take a look at Adam's utility, you will have tio write some logic to INSERT
INTO your Audit table the results of the sp
http://sqlblog.com/blogs/adam_machanic/archive/2009/03/30/who-is-active-v8-40-now-with-delta-power.aspx

"Jaison Jose" <Jaiso...@discussions.microsoft.com> wrote in message
news:41B90F87-90C2-47AF...@microsoft.com...