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.
Add a comment
|
3 Answers
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
-
-
1The dm_exec DMVs only capture active connections, once the connection is killed or closed it will no longer have information on that session. It will not retain the information so you will not see a logout time column. The only place I know that you could get logout time would be the default trace file (if enabled).– user507Commented Feb 15, 2012 at 14:42
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.
answered Feb 15, 2012 at 13:45
-
1It's a long-standing misconception that
>= 51is a user session, however it's a known issue that some system SPIDs can venture out into the user-SPID space. Much safer to useWHERE is_user_process = 1... Commented Feb 16, 2012 at 5:27 -
@AaronBertrand Ah, I didn't know that. Thanks for pointing that out. I've edited to reflect. Commented Feb 16, 2012 at 13:47
select s.login_name, *
from sys.dm_exec_connections c join sys.dm_exec_sessions s on c.session_id =s.session_id
