Need advice - tracking users 1

[katgirl]

Hi,

I have a situation that is driving me crazy.

We have a zillion (it seems) logins/users in our databases, and I know that not all of them are in use. I need to find out which ones are actually in use and which ones aren't. These were created long before I came on board, and it's a mess.

I was wondering how to best set up a trace using profiler to find out which users are actually active in the database. I would like to run it for 5 days, but without creating a humongous table. So, I need the least amount of data possible, while at the same time identifying active users.

Thanks much

 

[Catadmin]

Create your new trace, name it and point it to the File or the database table you want it to log to. A file won't mug up your database with extraneous data and you can save it to a separate HD from your database files.

On the second tab, stick with the Auto-selected "Security Audit" and "Sessions" counters. You can probably remove the "Stored Procedures" and "TSQL" counters. On the data Columns tab, I would at least keep the ApplicationName, DatabaseName, NTUserName, LoginName and DBUserName. StartTime and EndTime aren't necessary but are helpful in determining if people log in but never log out .

Last tab, Filters, you can use DBUserName to filter out user names you know you don't want to look at (Not Like) or only view specific user names (Like). Same with DatabaseName (You can exclude system DBs or specific archive DBs).

Then all you need to do is start the trace and have much fun watching all the boring logins go by. @=)

Hope this helps.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[katgirl]

thanks, catadmin

 

[Catadmin]

NP. @=)


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"