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:
|
1 2 3 4 5 |
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; |
|
1 2 3 4 5 6 7 8 9 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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; |
|
1 2 3 4 5 6 7 8 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
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; |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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.