Script to audit logged in users

[nubianwarrior]

I am not that great with sql scripting so I thought I'd ask for help after struggling to get my problem solved. I need a script that will give me a list and count of users logged into the sql server at one time and store this information in a table. I need this information to trend the number of concurrent users logged into the system.

Any help would be appreciated.

Thanks in advance.

 

[mutley1]

Do you mean logged into the SQL server as a whole or just logged into a specific application?

 

[nubianwarrior]

I mean logged into SQL SERVER as a whole. From time to time we get questions about how many people have been logged into the sql server at any given time and I want to be able to track this. Maybe have a script that runs a couple of times a day and store the results in a table for historical purposes.

 

[gmmastros]

Take a look at the sp_who and sp_who2 built in stored procedures. Anything with a SPID below 50 is an internal process (which you probably don't want to log).

You may also want to take a quick look at:

select * from master.dbo.sysprocesses Where spid >= 50

There's likely to be more information in there than you want, so, instead of select *, change columns instead.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mutley1]

George's post is probably yht best to follow, but if you have an audit table for your software then it might be worth looking at that.