Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to log/get all database activity for one user Expand / Collapse
Author
Message
Posted Friday, June 6, 2008 2:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 30, 2010 9:06 AM
Points: 6, Visits: 75
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'
Post #513218
Posted Friday, June 6, 2008 2:26 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Today @ 8:57 AM
Points: 6,634, Visits: 1,872
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #513219
Posted Friday, June 6, 2008 2:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 30, 2010 9:06 AM
Points: 6, Visits: 75
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?
Post #513236
Posted Saturday, June 7, 2008 4:30 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, August 14, 2014 8:21 AM
Points: 3,084, Visits: 1,433
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
Post #513379
Posted Monday, June 9, 2008 8:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 9, 2008 1:24 PM
Points: 92, Visits: 117
You can store the trace into a table and then execute the query to get specific data from one user only...
Post #513740
Posted Monday, June 9, 2008 11:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:08 PM
Points: 1,414, Visits: 4,540
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

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #513890
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse