Tutorial :How to tell who is logged in to SQL Server



Question:

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?


Solution:1

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.


Solution:2

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


Solution:3

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.


Solution:4

I think you're looking for sp_who or sp_who2


Solution:5

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.


Solution:6

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »