Sql-server – How to use `sys.dm_exec_connections` in combination with SUSER_SNAME()

sql serversql-server-2005

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.

Best Answer

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

Related Solutions

SQL Server Replication – How to Get Combination of Values from a Binary(8) Field?

Use the built-in bitwise operators to examine the column. For example

select
    schema_option & 0x01 AS  GeneratesObjectCreation,
    schema_option & 0x02 AS  GenerateCustomSP,
... etc.

SQL Server – How to Find Second-Greatest String in Each Group

Something like this would split the results into multiple columns, but you would need to know in advance the maximum number of employees per purchase_id:

With Ordered_cte As ( 
    Select employee,
        purchase_id,
        RowNo = Row_Number() Over (Partition By purchase_id Order By employee)
      From tbl_purchase_employee)
Select purchase_id,
    Employee1 = Max(iif(RowNo = 1, employee, Null)),
    Employee2 = Max(iif(RowNo = 2, employee, Null)),
    Employee3 = Max(iif(RowNo = 3, employee, Null))
  From Ordered_cte
  Group By purchase_id;

Best Answer

Related Solutions

SQL Server Replication – How to Get Combination of Values from a Binary(8) Field?

SQL Server – How to Find Second-Greatest String in Each Group

Related Question