How to tell which windows group login I used when logging in via windows authentication 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.
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 '&lt;DOMAIN\user&gt;', '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.