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;