November 8, 2007 at 12:37 pm
We found somebody logined our sql server 3 days ago. I want to keep track of what kind of sql statements this user used and what this user did. How can I find this information. Is there table or log to save statement excution history? How can I retrieve them? Thanks.
November 8, 2007 at 11:34 pm
Only thing I can suggest is starting a Profile Trace filtering on spid, or login, or whatever is unique and capturing. Unfortunately, this won't tell you what they did... but what they are doing ( since the start of the trace )
see SQL Profiler
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
November 8, 2007 at 11:36 pm
Also, in activity monitor... last batch column. Or right click on the session and view details.
grab the spid and execute
dbcc inputbuffer (spid)
perhaps just a hung connection that is inactive?
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
November 9, 2007 at 12:00 am
Since you're on SQL 2005, you can use one of the DMVs to retreive tha last statement executed, instead of InputBuffer that only returns 255 characters
SELECT er.session_id, blocking_session_id, wait_type, wait_time, wait_resource, database_id, login_time, login_name, command, st.text
FROM sys.dm_exec_requests er INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE session_id = @Spid_Of_Suspicious_Connection
Of course, it won't help you is the connection has been closed. Also, if the user ran a stored proc, you will see the create statement of the proc, not the exec of the proc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2007 at 12:53 pm
Thanks !
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply