Get Login Session count, Session Mode, User Name, Machine name in SQL Server

Get Login Session count, Session Mode, User Name, Machine name in SQL Server

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

Related posts

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.