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

Posted

in

by

Tags:


Related Posts

Comments

2 responses to “Get Login Session count, Session Mode, User Name, Machine name in SQL Server”

  1. Klevis Avatar
    Klevis

    Great.
    What i was looking for.

  2. deadlockgb Avatar

    Thank you!

Leave a Reply to deadlockgbCancel reply

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

Discover more from Jitendra Zaa

Subscribe now to keep reading and get access to the full archive.

Continue Reading