December 21, 2017 at 12:12 am
Hi All,
I am new to this forum and I have the following question:
- I need a query to get the following information from the SQL Server 2008 & 2012
1- who logged in sql db , (user name)
2- from where (system ip, system name)
3- At what time and date
4- What activity was performed ( for e.g. what query was executed)
Thanks in advance
December 21, 2017 at 1:04 am
aznabeel - Thursday, December 21, 2017 12:12 AMHi All,I am new to this forum and I have the following question:
- I need a query to get the following information from the SQL Server 2008 & 2012
1- who logged in sql db , (user name)
2- from where (system ip, system name)
3- At what time and date
4- What activity was performed ( for e.g. what query was executed)
Thanks in advance
For that you will have to setup some form of trace and/or auditing to capture what you need.
Points 1 2 and 3 can be done with a logon trigger
Point 4 will be the tricky one, you can create a server wide DDL trigger to capture all DDL events, but for your DML you will need to start adding DML triggers to all your tables or run traces to tie back the SPID to the statement.
December 21, 2017 at 1:45 am
Thom A - Thursday, December 21, 2017 1:38 AMThis might be easier with Extended Events on SQl Server 2012. I honestly can't recall if XE is available on 2008.
It is, but unusable (no GUI and minimal events). Server-side trace will work fine for these requirements.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply