Identify S-1-9-3 logins in sys.dm_exec_sessions

When looking at sys.dm_exec_sessions to see who is logged into a SQL Server instance, you may notice the login_name column contains a SID such as S-1-9-3-1474169822-1205489898-2971455952-561433448.

This can happen as a result of using EXECUTE AS USER = 'SomeUser'; to switch context to another user that doesn’t have a server-level account.

Luckily, you can simply look at some other columns in sys.dm_exec_sessions to determine the login used:

To determine the name of the database principal being impersonated, you can extend this using a little dynamic SQL:

This can be further extended to indicate the authentication path for logins that have connected via Active Directory group membership. To accomplish this, we need to make use of the xp_logininfo extended stored procedure, which provides a method of obtaining group members from Active Directory:

————————-

As a side note, SysInternals has a fantastic tool for querying Active Directory named, appropriately, AD Explorer.