Find Out Who Is Connected To The Database

From Wiki

There are several ways to find out how many people are connected to your SQL Server. The first way is to use the sysprocesses system table. Since server process identifiers less than 51 are system ones we will have to exclude those. This will work in SQL Server 2000, 2005 and 2008

tsqlLine number On/Off | Show/Hide | Select all

SELECT COUNT(*) FROM sysprocesses
WHERE spid > 50

Another way is to use the sp_who procedure, just look at spids greater than 50. This will work in SQL Server 2000, 2005 and 2008

tsqlLine number On/Off | Show/Hide | Select all

EXEC SP_WHO

Yet another way is to use the sp_who2 proc, this proc is undocumented and has a little more info than sp_who. This will work in SQL Server 2000, 2005 and 2008

tsqlLine number On/Off | Show/Hide | Select all

EXEC sp_who2

SQL Server 2005 introduced Dynamic Management Views, in this case we can just check if the is_user_process is 1 instead of checking for the value of spid. This will work in SQL Server 2005 and 2008

tsqlLine number On/Off | Show/Hide | Select all

SELECT COUNT(*) AS ConnectionCount
FROM sys.dm_exec_sessions
WHERE is_user_process = 1

If you want both system and user then you can do something like this

tsqlLine number On/Off | Show/Hide | Select all

SELECT COUNT(*) AS ConnectionCount,
CASE WHEN is_user_process =1 THEN 'UserProcess' ELSE 'System Process' END
FROM sys.dm_exec_sessions
GROUP BY is_user_process

Finally we take a look at the sys.dm_exec_connections Dynamic Management Views. This view only shows user connections. This will work in SQL Server 2005 and 2008

tsqlLine number On/Off | Show/Hide | Select all