SQL Server – Get Login Name of Integrated Security Account

loginssql serversql-server-2017

Let's say I'm a user in windows domain DC (DataCenter).
My user-name is DC\Somebody
I'm in group DC\Developers
Now SQL-Server has the login group called DC\Developers, and with that, I can login on the server with integrated security.

I'm now trying to get the SQL-Server login (by which I mean DC\Developers), but I can't figure out how. All I can get is the username (DC\Somebody).
But I want to get DC\Developers.
How can I do that ?

This are the functions I tried so far, without success:

SELECT -- http://blog.sqlconcepts.co.nz/2011/07/who-am-i.html 
        ORIGINAL_LOGIN() AS OriginalLogin
       ,SUSER_SNAME() AS CurrentContext 
       ,SESSION_USER AS SessionUser 
       ,SYSTEM_USER AS SystemUser
       ,CURRENT_USER AS CurrentUser 
       ,user AS "WhichUserIsThat?"
       ,ORIGINAL_LOGIN() AS OriginalLogin 
       ,USER_NAME() AS UserName

Also, what happens if multiple groups are added as sql-server logins, and I'm a member of them all, and then I login with integrated security. Which login will it take ? It appears to be the alphabetically first …

Best Answer

To get the AD groups with exsting logins on the instance that your login DC\Somebody belongs to

xp_logininfo

EXEC xp_logininfo 'DC\Somebody','all';

sys.login_token 

SELECT DISTINCT lt.name 
FROM sys.login_token lt
INNER JOIN  sys.server_principals  sp
ON lt.principal_id = sp.principal_id AND  sp.type != 'R'
WHERE lt.name NOT IN (SELECT name FROM sys.database_principals WHERE type = 'R' AND name IS NOT NULL) 
AND sp.type != 'R';

An example to validate this for other logins with impersonation

EXECUTE AS LOGIN ='DOMAIN\a-rvertongen';

SELECT DISTINCT lt.name 
FROM sys.login_token lt
INNER JOIN  sys.server_principals  sp
ON lt.principal_id = sp.principal_id
WHERE lt.name NOT IN (SELECT name FROM sys.database_principals WHERE type = 'R' AND name IS NOT NULL) 
AND sp.type != 'R';

REVERT;

Result

name
DOMAIN\a-rvertongen
DOMAIN\SERVER_RO
DOMAIN\SERVER_sysadmin
BUILTIN\Administrators

Also, what happens if multiple groups are added as sql-server logins, and I'm a member of them all, and then I login with integrated security.Which login will it take ?

Permissions for multiple AD groups

You will be granted the GRANT / DENY rights of each AD Group. DENY take precedence over GRANT permissions.

A more thorough write up in a previous answer by @Max Vernon can be found here.

Related Question