2

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 ...

3

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.

| improve this answer | |
  • Yea, aggregation of the rights, that hopefully, but the language seems to be taken from the first :) Is there a way to get this with a function ? I need the name in a trigger. – Quandary May 23 '19 at 8:10
  • @Quandary added a DMV that you can use – Randi Vertongen May 23 '19 at 8:11
  • OK thank you very much, that works. – Quandary May 23 '19 at 8:12
  • Yep. Just one small correction: the correct WHERE-clause is: WHERE lt.name NOT IN (SELECT name FROM sys.database_principals WHERE type = 'R' AND name IS NOT NULL) AND lt.name NOT IN (SELECT name FROM sys.server_principals WHERE type = 'R' AND name IS NOT NULL ) – Quandary May 23 '19 at 8:26
  • @Quandary great, thanks for pointing that out! Edited – Randi Vertongen May 23 '19 at 8:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.