use sys.dm_exec_requests and sys.dm_exec_sessions to find who are logged in to your SQL Server Instance

Many time we need to find who are logged in to SQL Server instance with which tool/program/software. SQL Server gives this very useful information to us with following two DMVs.

sys.dm_exec_requests
sys.dm_exec_sessions

I have written VIEW in my SQL Server Instance to capture all these necessary information.

here is the TSQL script for the same:

CREATE VIEW WhoisLoggedIn
AS
SELECT
ISNULL(sess.host_name, '') as host_name,
ISNULL(DB_NAME(req.database_id) , '')  as database_Name,
ISNULL(sess.program_name, '')   as ProgramName,
ISNULL(sess.login_name, '')  as login_name,
ISNULL(CAST(req.start_time AS VARCHAR), '')  as start_time,
DATEDIFF(MINUTE,ISNULL(req.start_time,GETDATE()),GETDATE())  as RunningMinutes,
req.Session_id

FROM
sys.dm_exec_requests req
INNER JOIN
sys.dm_exec_sessions sess
ON
req.session_id=sess.session_id
WHERE
--will eliminate all system processes
req.session_id >= 50
AND
sess.host_name IS NOT NULL

Once you create this VIEW in your SQL Server, you don’t need to remember complex query but to use only user friendly name “WhoisLoggedIn” with standard SELECT statement like this:

SELECT * FROM WhoisLoggedIn

Note: I have written one blog post in past which was finding currently running processes in SQL Server with the help of above given two DMVs.  click here to read it.

If you wish you can, even, use this VIEW in your .NET web page/windows application so that you don’t even need to connect to database directly to see the status.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations

2 thoughts on “use sys.dm_exec_requests and sys.dm_exec_sessions to find who are logged in to your SQL Server Instance

  1. Pingback: Performance Counter in SQL Server

Comments are closed.