How to log/get all database activity for one user

Question

How to log/get all database activity for one user

gurevich_larisa

SSC Veteran

Points: 280
More actions
June 6, 2008 at 2:24 pm

#380978

Hi everyone,
Activity monitor and system stored procedures can return the current activity of one user.
This code, for example shows all sql commands that a user is executing. I need to find a way to get the same information, but not for a current state, but for the whole day:
select sys.dm_exec_sessions.session_id,
sys.dm_exec_sessions.host_name,
sys.dm_exec_sessions.program_name,
sys.dm_exec_sessions.client_interface_name,
sys.dm_exec_sessions.login_name,
sys.dm_exec_sessions.nt_domain,
sys.dm_exec_sessions.nt_user_name,
sys.dm_exec_connections.client_net_address,
sys.dm_exec_connections.local_net_address,
sys.dm_exec_connections.connection_id,
sys.dm_exec_connections.parent_connection_id,
sys.dm_exec_connections.most_recent_sql_handle,
(select text from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as sqlscript,
(select db_name(dbid) from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as databasename,
(select object_id(objectid) from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as objectname
from sys.dm_exec_sessions inner join sys.dm_exec_connections
on sys.dm_exec_connections.session_id=sys.dm_exec_sessions.session_id
where login_name='XXXXX'

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 1

You'll want to run a server side trace to capture this information. You can build such a trace using SQL Profiler and then get the T-SQL to create and execute the trace on the SQL Server.

K. Brian Kelley
@kbriankelley

gurevich_larisa SSC Veteran Points: 280 More actions · Answer 2

Thanks for the quick reply.

Looks like trace allows to capture the status of logins, sessions, etc.

I basically need to capture all sql commands that were sent by a user (application)

What do I specify in that trace?

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 3

Larisa, you will be able to see the sql commands in the trace, it comes with the standard trace. I will advice you to be careful with making trace. http://www.microsoft.com/technet/abouttn/flash/tips/tips_020205.mspx this article will explain you why it is important to be careful with traces.

Good luck!

My blog

krammana Mr or Mrs. 500 Points: 546 More actions · Answer 4

You can store the trace into a table and then execute the query to get specific data from one user only...

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 5

write a query using the sessions and connections DMV's to dump data to a table. if you want you can get the SQL as well, but it's going to require a lot more space