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