April 4, 2012 at 9:21 am
Hi all,
I have been tasked to figure out which users are accessing which databases on a particular server running MSSQL 2008 R2 Express. I am green in this area, I assume a trace would work for this but I have no idea where to start/look for on that front. If anyone could point me to some resources or ideas I would greatly appreciate it. I'm also tasked to look at the Oracle side of this... I'm lost there even more 🙂
Thanks.
April 4, 2012 at 11:01 am
Do you need to store the data or do you just need snapshots of the data?
You can create a trace to get the Audit Login event. Or you could run a query regularly tha logs information from sp_qho, sp_who2 or sys.dm_exec_connections
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 4, 2012 at 1:59 pm
No need to store the data.
Is there a way to see which databases are being hit against using trace or sp_who/sp_who2? I don't see that information.
April 4, 2012 at 2:06 pm
sp_who and sp_who2 both return the column DBName.
With SQL Trace/Profiler you can add the Database_ID and Database_Name columns to the trace for the AuditLogin Event. Of course this will show the logins default database.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 4, 2012 at 2:24 pm
Thank you Jack that's exactly what I needed.
April 4, 2012 at 2:32 pm
I like sp_WhoIsActive.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply