sp_who

sp_who Provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.

Syntax of sp_who

sp_who [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]
[Via]

To see all the connections to your server, run sp_who2 without any parameters:

EXEC sp_who;

To see only active connections to your server:

EXEC sp_who 'active';

To see details about an individual process, pass in the process id:

EXEC sp_who '10';

To list a specific user’s process:

EXEC sp_who 'sa';

If you want to see the sp_who stored procedure use:

exec sp_helptext sp_who

one way to sort the results of the sp_who by any column, for example by dbname or login name etc. is to create a temporary table that matches the output of sp_who and then select the rows using order by clause.

Select * from temptable order by loginname;

There is also sp_who2 which is undocumented and it is same as sp_who but is more comprehensive that is it shows little extra information that sp_who.