How to tell which windows group login I used when logging in via windows authentication

Question

I created two SQL Server Logins which correspond to two windows groups:

MachineName\MyAppAmdin
MachineName\MyAppUser

Then in the database, I created two users with the same name and mapped them to the logins.

In the windows server, I added my domain account MyDomain\MyAccount to the MachineName\MyAppAmdin group.

Now I can pass windows authentication via MyDomain\MyAccount.

The thing is, I would like to know exactly which windows group login I'm using, but I'm not able to find out how.

I tried:

ORIGINAL_LOGIN()
SYSTEM_USER
SUSER_SNAME
SELECT * FROM dm_exec_sessions

All the above return MyDomain\MyAccount, but what I need to know is if I was logging in via the group membership in MachineName\MyAppAmdin.

To sum up, my question is:

Is there a way to tell exactly which Windows Group Login (or user) the current connection is using?

Or is there any way I can check if MyDomain\MyAccount is associated with a particular user or login?

I know I can use C# or command to resolve if a domain account belongs to specific windows group, but we have some new IT policies, so I'm thinking of a way to achieve a similar result by using TSQL.

Answer 1

Windows Authentication is token-based authentication, you can read about tokens here Access token and here Token Based Authentication

The general concept behind a token-based authentication system is simple. Allow users to enter their username and password in order to obtain a token which allows them to fetch a specific resource - without using their username and password.

---

An access token is generated by the logon service when a user logs on to the system and the credentials provided by the user are authenticated against the authentication database. The authentication database contains credential information required to construct the initial token for the logon session, including its user id, primary group id, all other groups it is part of, and other information. The token is attached to the initial process created in the user session and inherited by subsequent processes created by the initial process.

So when you use Windows Authentication to logon, you present to server your Windows token.

You can see all the server principals that are part of your login token using this code:

select *
from sys.login_token
--where principal_id > 0; -- uncomment here to see only mapped principals

If you want to explore login token of another login, you should first impersonate it:

execute as login = 'some_login';

select *
from sys.login_token
where principal_id > 0;

revert;

Of course you should have IMPERSONATE permission on some_login to be able to impersonate it.

So your permissions on server are defined based on the "sum" of the permissions of all the principals that make part of your token. DENY as always has precedence on GRANT so if you are a member of two Win groups one of wich has grant and other deny on some object, you'll be denied to access it.

Answer 2

Windows Authentication in SQL Server doesn't work exactly like that. When you log in as a domain user who has an associated login, but also has access via a domain group that has an associated login, the access is determined by the combined DENY/GRANT permissions and SQL\DB role membership assigned to both the user login and the group login.

There is no concept of being 'logged in' as the group, the group is simply a container to provide access to a collection of domain users based on their membership in this group in Active Directory.

You can check the various access paths for a particular user by running this command:

EXEC xp_logininfo '<DOMAIN\user>', 'all'

This will list out the various access paths for a user, i.e. all the group logins and user logins this Windows account is linked to.

Answer 3

While there is good info in both HandyD's answer and sepupic's answer, there is still some clarification needed.

1. When you log in using Windows Authentication, your security context includes all of the Logins mapped in sys.server_principals that match your current Windows security context. This can be a Windows Login and/or one or more Windows Groups. If your Windows account is in 5 groups and 3 of those groups are registered as Logins in SQL Server, but no Login for you specifically, your security context will be just those 3 groups. If you then added a Login for your Windows account specifically, then logging in would give you a security context of your Windows Login plus all 3 of those mapped groups.

2. xp_logininfo is helpful in that it can show the matching mapped Groups and/or Login for accounts without having to log in as them or impersonate them. And it can let you see the members of a Windows Group (if the Windows Group is registered in SQL Server as a Login).

   What this system stored procedure can't show is:

   1. the rest of the security context for a particular Login / Group that determines the effective permissions. For instance-level permissions this would include Server Roles that the Login and/or Group(s) are members of. For database-level permissions this would include Database Roles in any database that the Login has a mapping for
   2. Logins that are direct to the database without having a Login at the instance-level. This is how Contained Databases work (but it's ok that xp_logininfo doesn't show anything for Contained DBs since it only works inside of them if the instance-level collation is Latin1_General_100_CI_AS_KS_WS_SC due to a bug I just discovered while testing this: xp_logininfo gets "Msg 468, Level 16, State 9: Cannot resolve the collation conflict..." when DB collation doesn't match instance collation)

3. sys.login_token is helpful for seeing the full security context, including the Windows Login (if there is one mapped) and/or any Windows Groups (if any are mapped), as well as server-level roles that the Login and/or Groups are members of. "Effective" permissions are based on ALL permissions across all security tokens, and a DENY overrides any GRANTs. So you can be granted a permission to something via membership in Group 1, but Group 2 is a member of a server-level role that has been denied that permission (or a parent permission), and so you are effectively denied that permission.

   What this DMV can't show is:

   1. any info for Logins other than the current Login
   2. members of Windows Groups

4. sys.user_token is helpful for seeing the full security context at the database-level. The security tokens can be different per each database so effective permissions are always based on the current database when this DMV is checked. This DMV is the more relevant info when directly logging into a Contained DB.

Answer 4

This is what I normally use to find out what user is being used:

it doesn't though, return the current Windows group via which the user is being granted access to the database.

-- get the current login name
-- marcelo miorelli
-- 26-11-2013

DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(), CHARINDEX('\', SUSER_SNAME()) + 1, LEN(SUSER_SNAME()))
SELECT  [THE_SERVER]= @@SERVERNAME
        ,[DB_NAME] =DB_NAME()
        ,[@USER]=@USER 
        ,[SUSER_SNAME()]=SUSER_SNAME()
        ,[SYSTEM_USER]=SYSTEM_USER
        ,[USER_NAME()]=USER_NAME() 
        ,[CURRENT_USER]=CURRENT_USER
        ,[ORIGINAL_LOGIN()]=ORIGINAL_LOGIN()
        ,[USER]=USER
        ,[SESSION_USER]=SESSION_USER

and to test the above, or sometimes to test some permission or procedure I do something similar to the example below:

-- to check how a specific procedure will run under a different account
EXECUTE AS LOGIN='mycompany\Celcatsupport'

-- checking who I am running the commands as (impersonating)
DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(), CHARINDEX('\', SUSER_SNAME()) + 1, LEN(SUSER_SNAME()))

SELECT  [THE_SERVER]= @@SERVERNAME
        ,[DB_NAME] =DB_NAME()
        ,[@USER]=@USER 
        ,[SUSER_SNAME()]=SUSER_SNAME()
        ,[SYSTEM_USER]=SYSTEM_USER
        ,[USER_NAME()]=USER_NAME() 
        ,[CURRENT_USER]=CURRENT_USER
        ,[ORIGINAL_LOGIN()]=ORIGINAL_LOGIN()
        ,[USER]=USER
        ,[SESSION_USER]=SESSION_USER

--reverting back to my original login
REVERT

the following script does not give you which group was used for the current login but it has been very useful to me in many occasions

--the following query will give you 
--all the available windows group that 
--have login in the current server
--and @NTLogin belongs to

                    declare @NTLogin nvarchar(128)
                    select @NTLogin = 'mycompany\myuser'

                    DECLARE @UserList TABLE (
                             [Account Name] nvarchar(128)collate Latin1_General_CI_AS,
                             [Type] nvarchar(128) collate Latin1_General_CI_AS,
                             [Privilege] nvarchar(128) collate Latin1_General_CI_AS,
                             [Mapped Login Name] nvarchar(128)collate Latin1_General_CI_AS,
                             [Permission Path] nvarchar(128) )


                    INSERT INTO @UserList EXEC master.dbo.xp_logininfo @NTLogin, 'all' --insert group information

                    IF EXISTS (SELECT * FROM @UserList WHERE [Type] = 'group') --only if it's a group
                       INSERT INTO @UserList EXEC master.dbo.xp_logininfo @NTLogin, 'members' --insert member information

                    SELECT  [Server Name] = @@SERVERNAME,
                            [Account Name],
                             [Type],
                             [Privilege],
                             [Mapped Login Name],
                             [Permission Path]                  

                    FROM @UserList

Answer 5

I found IS_MEMBER() can verify if the current user if a member of the windows group I created.