CODEWITHSUNDEEP
sqlsql-server
mellerbeck
mellerbeck

Reputation: 317

How to capture queries run by a specific user in SQL Server

Messed with SQL Sentry, I guess it could be done with a trace as well. Any recommended ways to watch/log the queries of a particular user? Logging it anytime they run it. i.e. if they ran it at night?

Upvotes: 0

Views: 7293

Answers (1)

yW0K5o
yW0K5o

Reputation: 943

Try to use SQL Server system functions: Execution Related Dynamic Management Views and Functions (Transact-SQL)http://msdn.microsoft.com/en-us/library/ms188068.aspx

For example:

    1. Get SessionID by user:

    exec sp_who;

    1. Use this:

    select a.session_id, st.text as QueryText from sys.dm_exec_connections a CROSS APPLY sys.Dm_exec_sql_text(a.most_recent_sql_handle) st where a.session_id = SessionID;

Upvotes: 2

Related Questions