Dec 26

Get all the current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine

Have seen many of the forum users are posting question on how to find active user sessions in an instance of Microsoft SQL Database Engine. 


In order to get the current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine, we can use the “sp_who2” SQL stored procedure. 


Following is the definition provided for “sp_who2“ in the TechNet

Provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.

“sp_who2” return a data set as a output and in order to capture the data set we can use a temporary table. Following is the sample code that would return all the current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine.

 DECLARE @Table TABLE(  
     SPID INT,  
     Status VARCHAR(MAX),  
     LOGIN VARCHAR(MAX),  
     HostName VARCHAR(MAX),  
     BlkBy VARCHAR(MAX),  
     DBName VARCHAR(MAX),  
     Command VARCHAR(MAX),  
     CPUTime INT,  
     DiskIO INT,  
     LastBatch VARCHAR(MAX),  
     ProgramName VARCHAR(MAX),  
     SPID_1 INT,  
     REQUESTID INT  
 )  
 INSERT INTO @Table EXEC sp_who2  
 SELECT *  
 FROM  @Table  
 WHERE DBName = 'DatabaseName'


Please provide your feedback with a comment.
Thank you and Regards,
Tharanga Chandrasekara

Click on a star to rate it!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

2 comments

  1. This approach works well for version of NAV prior to 2013. After that the login_name in SQL or SQL Trace is displayed as the NAV SERVICE account – NT AUTHORITYNETWORK SERVICE – NOT the user's login name which, in most cases, would be their Windows credential.

    It would be nice to be able to obtain the user info to aid in trouble-shooting locking/blocking.
    – Stu

  2. Thranga,
    This is all well and good except that in Nav2015 the only information in SQL (either SQL trace or selecting info from sys.dm_exec_sessions, references the Nav SERVICE login – NT AUTHORITYNETWORK SERICE – and NOT the user's login.

    In our case we are using windows logins and it would be very handy to get the user's credential in the database for each session in order to investigate locking and blocking.
    – Doug

Comments have been disabled.