Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Feb 15 '12 at 18:18

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 4 down vote accepted

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
share|improve this answer
    
How do I get logout time? –  Bruno Feb 15 '12 at 14:37
1  
The 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). –  Shawn Melton Feb 15 '12 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.

share|improve this answer
1  
It's a long-standing misconception that >= 51 is a user session, however it's a known issue that some system SPIDs can venture out into the user-SPID space. Much safer to use WHERE is_user_process = 1... –  Aaron Bertrand Feb 16 '12 at 5:27
    
@AaronBertrand Ah, I didn't know that. Thanks for pointing that out. I've edited to reflect. –  Thomas Stringer Feb 16 '12 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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.