May 23, 2006 at 1:06 pm
I have been asked to search all of the MS SQL Server instances and gather a list of distinct users that are accessing them.
Does anyone know of a script or utility that could be used to do this? I know how to get the number of connections at a point in time, but I need to know the number of distinct users that have connected and if possible the application or method they connected using.
Thanks,
Erik
May 24, 2006 at 2:03 am
you can get this info from sysprocesses if you're trying to get point in time figures - failing that if it's sql users then a count of syslogins. If you've got roles and NT groups and connection pooling then really your only way is to query sysprocesses on a regular basis. There are a couple of perfmon counters but neither give the whole picture. Depends exactly what you want and how your users connect.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 24, 2006 at 11:08 am
I would use Profiler trace with Audit Login, Audit Logout and Audit Login Failed events, set the rollover size 5 MB, once the file rolls over and the previous file is in the closed state select from the trace file using fn_trace_gettable using Distinct keyword. Or group by user name to get a number of logins per user
Regards,Yelena Varsha
May 24, 2006 at 12:24 pm
You quest may be further complicated by applications as well. Some applications use multiple connections, some applications use generic IDs with application security ID's not visible to sysprocesses or to profiler. Also, if you are using the sysrocessess don't forget to ignore spids <50 - these are system spids.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply