CODEWITHSUNDEEP
sql-servert-sql
PNPTestovir
PNPTestovir

Reputation: 307

List of queries run

I look for DMV for SQL Server 2008 R2 which shows me all queries 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?

Upvotes: 0

Views: 155

Answers (1)

Shahar Gvirtz
Shahar Gvirtz

Reputation: 2438

They query you wrote won't give you a full history, but gives you the cached execution plans, which can get empties (usually in SQL Server restart).

If you want to keep track of all the user activity, you have some options:

Use SQL Server Profiler to store all the user's activity

You can create SQL Server Profiler session that track the event SQL:StmtStarting on the database you want. You can also use wildcards to track only specific queries. The results of the profling sessions can be stored in a table. You can make sure the profiling session starts automatially when the server starts and stay up all the time using SQL Server agent job.

The main cons of this, is that Profiler make a serious performance impact.

Extended Event session

The concept is actually very similar to the profiler session, except you'll use a newer mechanism called Extended Events.

The relevant event is sql_statement_starting, and like the Profiler, you can store the data into file (which can be queried), set limits, filters etc.

The performance of the Extended Event are usually way better than the profiler. and there is much smaller impact on the whole server performance when having active extended event tracking session.

SQL Server Auditing

You can also create an audit on SQL Server level, and then create AUDIT Specification in your database (in SSMS, in the object explorer, under the database->Security->Database Audit Specifications

You can audit SELECT, DELETE etc. and you can choose where the data will be saved (file / event log...).

Hope this helps.

Upvotes: 2

Related Questions