Follow sqlserver-dba.com

Daily & Exclusive Content

Delivered by FeedBurner

SQLServer-DBA.com Links

Dba_db2_button

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

02 May,2018 by Jack Vamvas

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)


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

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


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer