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 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.
Note: Microsoft Books online is a default reference of all articles but examples and explanations