sys.dm_exec_sessions SP stores the login information about users like username, login time, machine name, authentication mode (Windows or SQL).Therefore we can easily determine the current state of SQL server like Session count, User name, machine name etc. Use below query to get the result:
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
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
No comments:
Post a Comment