CODEWITHSUNDEEP
.netsql-server
Jeff Stock
Jeff Stock

Reputation: 3856

How to tell who is logged in to SQL Server

I am designing an application and I'm looking for the best way to tell who is currently logged into SQL server. I will use the Activity Monitor built into SSMS, which shows a list of all processes accessing the server - very cool.

However, if in my .NET code it uses a separate connection each time I access the database then how will I be able to tell who is currently logged in? If a user is just looking at a screen and not retrieving data from the database at that moment then they wouldn't show up correct?

Upvotes: 9

Views: 95248

Answers (6)

C. Ross
C. Ross

Reputation: 31878

Your assertion is correct, users not performing database actions would not be visible. You will really need to store some sort of "Logged In Users" information manually.

Upvotes: 2

Jonathan Kehayias
Jonathan Kehayias

Reputation: 3412

The DMV sys.dm_exec_sessions can be used to find connected sessions. You can join it to sys.dm_exec_requests to get request information if it is actively running, or to get connection specific information you can join to sys.dm_exec_connections. The session_id is used for all of the joins.

I'd personally use DMV's over the older sp_who stored procedures. As others have pointed out, if the connection closes, you won't see it so if you want to track between connections, you'd have to consider another method of caching the connection information.

Upvotes: 6

user110714
user110714

Reputation:

In MSSQL server you can run the following command...

exec sp_who2

This will give you a lot of information including who is logged in, application name, current command, so on and so forth.

Thanks

Upvotes: 22

Paulo Santos
Paulo Santos

Reputation: 11587

I think you're looking for sp_who or sp_who2

Upvotes: 3

Michael Petrotta
Michael Petrotta

Reputation: 60972

Maybe, maybe not. You're correct that being logged into your application does not require a user to be logged into the database - in fact, that concept doesn't really exist. Activity Montitor (and, usefully, sp_who2) will show only active connections - those connections actively in use.

That picture changes if you use pooled connections ("Pooled=true", I believe, in your connection string). Do that, and a "closed" client connection will remain active, and you'll see that connection in the monitor. This article has some more detail on that.

If I were you, though, I'd architect a different way to track active users, one that doesn't rely on pooled connections.

Upvotes: 10

Scott Ewers
Scott Ewers

Reputation: 664

You are correct. If coded correctly, the connection will be created when data is saved or retrieved, and closed immediately afterward.

Assuming you are more interested in who is logged into the application than who is logged into the database server, you could persist session information in the database, including user ids. Then you could simply query the session database to discover who is online. There may be better solutions depending on the development technologies you are using.

Upvotes: 2

Related Questions