In SQL server sys.dm_exec_sessions Stores the login information about users like username, authentication mode (Windows or SQL), login time, machine name and therefore we can easily determine the current state of SQL server like Session count, User name, machine name etc.
To achieve this below query is used:
SELECT 'Authentication Method'=( CASE WHEN nt_user_name IS not null THEN 'Windows Authentication' ELSE 'SQL Authentication' END), login_name AS 'Login Name', ISNULL(nt_user_name,'N/A') AS 'Windows Login Name', COUNT(session_id) AS 'Session Count', host_name As 'Host' FROM sys.dm_exec_sessions GROUP BY login_name,nt_user_name,host_name
In output you will get the detail of users login with there machine name and session
Leave a Reply to deadlockgbCancel reply