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:

SELECT SessionID = des.session_id
	, LoginName = des.login_name
	, OriginalLoginName = des.original_login_name
FROM sys.dm_exec_sessions des
WHERE des.is_user_process = 1;
SessionID  LoginName                                               OriginalLoginName
---------- ------------------------------------------------------- --------------------
51         NT AUTHORITY\SYSTEM                                     DOMAIN\USER1
52         S-1-9-3-1474169822-1205489898-2971455952-561433448      SQLServerUser1
53         DOMAIN\USER2                                            DOMAIN\USER2
54         DOMAIN\USER2                                            DOMAIN\USER2
55         S-1-9-3-1474169822-1205489898-2971455952-561433448      DOMAIN\USER1
56         S-1-9-3-1474169822-1205489898-2971455952-561433448      DOMAIN\USER1

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

DECLARE @cmd NVARCHAR(MAX);
SET @cmd = '';

SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE '
UNION ALL
' END + 
'SELECT SessionID = des.session_id
	, DbName = ''' + d.name + '''
	, LoginName = des.login_name
	, OriginalLoginName = des.original_login_name
	, Impersonated = COALESCE(dp.name, '''')
FROM sys.dm_exec_sessions des
	LEFT JOIN ' + QUOTENAME(d.name) + '.sys.database_principals dp ON des.security_id = dp.sid
WHERE des.database_id = ' + CONVERT(NVARCHAR(5), d.database_id) + '
	AND des.is_user_process = 1'
FROM sys.databases d;

SET @cmd = @cmd + '
ORDER BY SessionID;'

EXEC sp_executesql @cmd;
SessionID  DbName     LoginName                   OriginalLoginName    Impersonated
---------- --------- ---------------------------- -------------------- ----------------
51         master     NT AUTHORITY\SYSTEM         DOMAIN\USER1
52         database1  S-1-9-3-1474169822-120...   SQLServerUser1       SQLServerUser2
53         msdb       DOMAIN\USER2                DOMAIN\USER2 
54         msdb       DOMAIN\USER2                DOMAIN\USER2 
55         database1  S-1-9-3-1474169822-120...   SQLServerUser1       SQLServerUser2
56         database1  S-1-9-3-1474169822-120...   SQLServerUser1       SQLServerUser2

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:

DECLARE @cmd NVARCHAR(MAX);
SET @cmd = '';

SET NOCOUNT ON;
IF COALESCE(OBJECT_ID('tempdb.dbo.#GroupMemberList'), 0) <> 0
BEGIN
	DROP TABLE #GroupMemberList;
END
CREATE TABLE #GroupMemberList
(
	[account name] SYSNAME
	, [type] CHAR(8)
	, [privilege] CHAR(9)
	, [mapped login name] SYSNAME
	, [permission path] SYSNAME
);

DECLARE cur CURSOR LOCAL STATIC FORWARD_ONLY
FOR
SELECT 'EXEC xp_logininfo @acctname = ''' + sp.name + ''', @option = ''members'''
FROM sys.server_principals sp
WHERE sp.type_desc = 'WINDOWS_GROUP';
OPEN cur;
FETCH NEXT FROM cur
INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO #GroupMemberList
	EXEC sp_executesql @cmd;
	
	FETCH NEXT FROM cur
	INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

SET @cmd = '';

SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE '
UNION ALL
' END + 
'SELECT SessionID = des.session_id
	, DbName = ''' + d.name + '''
	, LoginName = des.login_name
	, OriginalLogin = des.original_login_name
	, Impersonated = COALESCE(dp.name, '''')
	, [Group] = COALESCE(GroupMembers.GroupName, '''')
FROM sys.dm_exec_sessions des
	LEFT JOIN ' + QUOTENAME(d.name) + '.sys.database_principals dp ON des.security_id = dp.sid
	LEFT JOIN (
		SELECT AccountName = gml.[account name]
			, GroupName = sp.name
			, sp.sid
		FROM #GroupMemberList gml
			INNER JOIN sys.server_principals sp ON gml.[permission path] = sp.name
		) GroupMembers ON des.original_login_name = GroupMembers.AccountName
WHERE des.database_id = ' + CONVERT(NVARCHAR(5), d.database_id) + '
	AND des.is_user_process = 1 '
FROM sys.databases d;

SET @cmd = @cmd + '
ORDER BY SessionID;'

EXEC sp_executesql @cmd;
SessionID DbName LoginName               OriginalLogin   Impersonated    Group
--------- ------ ----------------------- --------------- --------------- ------
51        master NT AUTHORITY\SYSTEM     DOMAIN\USER2                   
52        db1    S-1-9-3-1474169822-120  SQLServerUser1  SQLServerUser2  
53        msdb   DOMAIN\USER1            DOMAIN\USER1                    
54        msdb   DOMAIN\USER1            DOMAIN\USER1                    
55        db1    S-1-9-3-1474169822-120  SQLServerUser1  SQLServerUser2  
56        db1    S-1-9-3-1474169822-120  SQLServerUser1  SQLServerUser2  
57        msdb   DOMAIN\USER1            DOMAIN\USER1                    
58        db1    S-1-9-3-1474169822-120  SQLServerUser1  SQLServerUser2  
59        db1    S-1-9-3-1474169822-120  SQLServerUser1  SQLServerUser2  
67        tempd  DOMAIN\USER3            DOMAIN\USER3                    DOMAIN\DB

————————-

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