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

us1

 

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

 

us2

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

us3

 

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.

7 thoughts on “SQL Server find users connected to databases”

  3. Hi Jim,
    I have a similar issue and I thought this script would help but it just listed my ID.

    I work with a batch scheduling application that uses a Service ID to access the database. The previous Admin retired and didn’t leave much in the way of documentation, and now the compliance team is now asking if I can tell them what the Service ID is so they can check to see if the password needs to be changed.

    Is there a way to determine current active sessions and the usernames associated with them?

    (fyi – it’s SQL v15.0)

    Like

    Reply

    1. You can try the built in stored procedure: SP_WHO2. The results are not formatted fancy, and you have to scroll through the results to find what you are looking for.

      Let me know if that helps.

      Like

      Reply

    2. I know this is a year old, but someone else might be looking for this info.

      To find current, active sessions (different from connected users), download and run the procedure sp_WhoIsActive. It will give you all the information you’re looking for and more.

      Like

      Reply

Leave a reply to Lukas Cancel reply