How to get the AD groups of a login with sys.login_token

Question: I’d like to get the list of AD groups of a login currently logged into a SQL Server.   I’ve read your post on xp_logininfo limitations – and one of the issues I’m experiencing is that xp_logininfo only returns details from AD global groups not universal groups. This is limiting my ability to identify non global groups using xp_logininfo.

Is there an alternative method from within SQL Server?

Answer: There are alternatives I use – both from within and outside of SQL Server. From without you can use Powershell cmdlets – which are powerful ways of querying the AD – but then you have to do all sorts of joins with currently logged in users .

An effective method to query from within SQL Server is to the sys sys.login_token or sys.user_token.

According to MS SQL Server BOL:

Sys.user_token - Returns one row for every database principal that is part of the user token in SQL Server.

sys.login_token - Returns one row for every server principal that is part of the login token

An easy method to use is grab the domain users id , and execute the query to return the rows from within the security context of the login – aka impersonation

For example:

EXECUTE AS LOGIN='DOMAIN\myID';

              select * from sys.login_token

REVERT;

The great thing about these results is iterate through the nested groups giving you greater insight into the users permissions path

As part of the resultset , there is a principal_id column , mapping to sys.server_principals . Use this information for more detailed information

If you’re using sys.user_token than principal_id is mapped to sys.database_principals

 Powershell Script Library for DBA (SQL Server DBA)