To get SQL Server all database sessions uses the system stored procedure named SP_WHO or uses the system view dynamic management named sys.dm_exec_sessions.
SP_WHO - show information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine.
sys.dm_exec_sessions - returns one row per authenticated session on SQL Server.
Syntax
sp_who [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ] ;
SELECT * FROM sys.dm_exec_sessions ;
Example
USE master;
GO
EXEC sp_who;
GO
SELECT * FROM sys.dm_exec_sessions;
SELECT login_name,COUNT(session_id) AS session_no
FROM sys.dm_exec_sessions
GROUP BY login_name;
See also: SP_WHO