DMV Query to identify all active SQL Server Sessions

You may already be aware that I am a big advocate of the SQL Server Dynamic Management Views (DMV’s) and the benifits that they bring to the Database Administrator. They certainly came in handy with a requirement I had recently whereby I needed to identify the IP Address of a specific SQL Server Login and so I would like to share my solution with you.

The query below identifies all currently active SQL Server user connections by their SQL Server Login name. It provides details of the IP address that the connection is sourced from, along with the number of sessions and connections that the SQL Server Login is currently responsible for.

SELECT
    B.login_name,
    A.client_net_address,
    NoOfConnections = COUNT(*)
FROM
    sys.dm_exec_connections A
		INNER JOIN sys.dm_exec_sessions B ON
			A.session_id = B.session_id
GROUP BY
    login_name,
    client_net_address

This is achieved by using two of SQL Server DMV’s:

• sys.dm_exec_connections
• sys.dm_exec_sessions