SQL Server find users connected to databases

If you need to find out who is currently connected to your databases or how many users are currently connected, the two scripts below are life savers.

Here are two quick ways to see who is connected to your server.

The first script shows the count of users attached to each database.  In my database below there are 8 connections total amongst all user databases on the server.

SELECT @@ServerName AS server
 ,NAME AS DatabaseName
 ,COUNT(STATUS) AS number_of_connections
 ,GETDATE() AS Query_Run_Time
FROM sys.databases sd
LEFT JOIN sysprocesses sp ON sd.database_id = sp.dbid
WHERE NAME not in ('master','model','msdb','tempdb')
GROUP BY NAME

This second script shows the actual usernames and machines that are connected.  It also shows the time the user connected and what application they are running.

SELECT @@ServerName AS SERVER
 ,NAME
 ,login_time
 ,STATUS
 ,hostname
 ,program_name
 ,nt_username
 ,loginame
FROM sys.databases d
LEFT JOIN sysprocesses sp ON d.database_id = sp.dbid
WHERE  NAME not in ('master','model','msdb','tempdb')
 AND loginame IS NOT NULL order by 2

Below is the .Net app that shows up on line 7 above that shows as being connected.

I have run these scripts on Sql Server 2008 through 2016.  They may work on earlier and later version but I do not have those versions to test on.

Happy computing.