How do I use `sys.dm_exec_connections` in combination with SUSER_SNAME()?

Question

How do I use sys.dm_exec_connections in combination with SUSER_SNAME()?

I want to see the username corresponding to each record in sys.dm_exec_connections.

Answer 1

As others have already pointed out the info you want is in sys.dm_exec_sessions. However if you want to pull from both DMVs this would work:

SELECT c.connect_time
 , s.login_time
 , s.host_name
 , s.login_name
FROM sys.dm_exec_connections AS c
   JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id

Answer 2

If you execute sp_who2 you'll get the SPID as well as the Login associated with it.

Or you could just do:

select *
from sys.dm_exec_sessions

This will give you all sessions, and if you just want user sessions then you could go with this:

select *
from sys.dm_exec_sessions
where is_user_process = 1

This will give you the SPID, host name, login name, status, etc.

Answer 3

select s.login_name, * 
from sys.dm_exec_connections c join sys.dm_exec_sessions s on  c.session_id =s.session_id