How to Find Users Connected to SQL Server (T-SQL)

by

Checking which users are connected to a SQL Server can be useful for both performance and security. It helps database admins see who’s using the system, what applications they’re running, and from where. This makes it easier to troubleshoot issues like slow queries or high resource usage. It also helps spot unusual or unauthorized access, such as logins from unknown machines or at odd times. Regularly reviewing active connections is a simple way to keep the server running smoothly and securely.

Below are five queries we can use to check which users are connected to a SQL Server instance.

1. Using sys.dm_exec_sessions

The sys.dm_exec_sessions view returns one row per authenticated session on SQL Server. We can return everything from this view like this:

SELECT * FROM sys.dm_exec_sessions;

But in most cases we would want to reduce the output. This view returns rows and columns that we might not be interested in. So a query like the following might be more useful:

SELECT 
    session_id, 
    login_name, 
    status, 
    host_name
FROM 
    sys.dm_exec_sessions
WHERE 
    is_user_process = 1;

This query uses WHERE is_user_process = 1 to remove system sessions from the result (this column is 0 for system sessions and 1 for user sessions). In other words, we use this filter to narrow our results down to just user sessions. Obviously, we would only do this if we don’t want to see system sessions.

2. Using sys.dm_exec_sessions and sys.dm_exec_connections

We can join sys.dm_exec_sessions with sys.dm_exec_connections to include the user’s IP address. Here’s a query that shows all active user sessions, along with host and client IPs:

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    c.client_net_address
FROM 
    sys.dm_exec_sessions s
JOIN 
    sys.dm_exec_connections c ON s.session_id = c.session_id
WHERE 
    s.is_user_process = 1;

3. Group by Login for Count of Connections

We can use the following query to identify how many sessions each user has open:

SELECT 
    login_name,
    COUNT(*) AS connection_count
FROM 
    sys.dm_exec_sessions
WHERE 
    is_user_process = 1
GROUP BY 
    login_name;

4. Using sp_who or sp_who2

We can run the sp_who stored procedure to list current users, their session info, database, and status:

EXEC sp_who;

Or we can use sp_who2 to get some more columns (like CPUTime, DiskIO, etc):

EXEC sp_who2;

5. Using sys.sysprocesses (older method, for compatibility)

The sys.sysprocesses system compatibility view is actually deprecated, but it still works:

SELECT 
    spid,
    loginame,
    hostname,
    program_name,
    dbid,
    status
FROM 
    sys.sysprocesses
WHERE 
    loginame IS NOT NULL;

As it turns out, the sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests dynamic management views map to the sys.sysprocesses view.

Given this view is deprecated, you should only try it if the other options on this page don’t work (for example if you’re working with a legacy system).

So those were five queries that can help you find the users connected to SQL Server.