Sql-server – How to tell which windows group login I used when logging in via windows authentication

loginsSecuritysql serversql-server-2012t-sql

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.

Best Answer

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.

Related Question