All active users in server

–1– To check last login time of sql server login

SELECT MAX(login_time) AS [Last Login Time], login_name [Login]
FROM sys.dm_exec_sessions
GROUP BY login_name;

–2–To check the last login date/time of a connection

SELECT MAX(login_time) AS [Last Login Time], login_name [Login]
FROM sys.dm_exec_sessions
GROUP BY login_name
order by 1 desc

–3–To check the number of connections per DB in a server

SELECT @@ServerName AS server,
NAME AS dbname,
COUNT(STATUS) AS number_of_connections,
GETDATE() AS timestamp
FROM sys.databases sd
LEFT JOIN sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4
GROUP BY NAME
Unknown's avatar

Published by Manish Kaushik

Leave a comment