Get Connection Counts by IP and Login
Applicability:
SQL Server 2000: Not Supported
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Credits:
Author: Unknown
Date: 4 Apr 2012
Description
Retrieve summaries of connections to the SQL server by both IP and login name
Code
Summary of Connections by IP:
-- Get a count of SQL connections by IP address
SELECT
ec.client_net_address,
es.[program_name],
es.[host_name],
es.login_name,
COUNT(ec.session_id) AS [connection count]
FROM sys.dm_exec_sessions AS es
INNER JOIN sys.dm_exec_connections AS ec
ON es.session_id = ec.session_id
GROUP BY
ec.client_net_address,
es.[program_name],
es.[host_name],
es.login_name
ORDER BY
ec.client_net_address,
es.[program_name];
Summary of Connections by Login Name:
-- Get a count of SQL connections by login_name
SELECT
login_name,
COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions
GROUP BY login_name
ORDER BY login_name;