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.
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
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,
–will eliminate all system processes
req.session_id >= 50
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.
Note: Microsoft Books online is a default reference of all articles but examples and explanations