How to find list of AD groups of a user from SQL Server

09 June,2020 by Jack Vamvas

An application owner reported a sudden loss of access to database tables . They'd been reading from the sql tables - but logged off for awhile , logged back on and couldn't see the tables.  I logged on , could see the tables , so discounted any possibilities they may have been dropped. 

So the next question was to confirm the user logon still had the relevant privileges to see and read from the tables.    I needed to check the user was in certain Active Directory Groups. I wanted to check this from the SQL Server side - and one of the most useful methods  is to use sys.login_token or sys.user_token

If you want to read more details about sys.login_token & sys.user_token , read How to get the AD groups of a login with sys.login_token  

A common troubleshooting method is to impersonate the login . To impersonate the login use EXECUTE AS LOGIN = , then use the sys.login_token to obtain a list of groups to which the login belong (including all the AD groups and server level roles. Use the principal_id column linking to the sys.server_principlas system view . 

If you want to drill into specific database details use the sys.user_token - to obtain AD groups and roles linked to the specific database. In the sys.user_token scenario the principal_id  references the sys.database_principals view

You will have to use the EXECUTE AS LOGIN =  but once you are impersonating the login you can query sys.login_token to get a list of groups the login belongs to. This includes any server level roles and all of the AD groups. There is a principal_id column that links to the sys.server_principals system view. It will be filled in for all of the server roles and for an AD groups that have an entry in sys.server_principals.

To get more database specific information you can go to the database you are interested in and use sys.user_token to get a list of roles/AD groups associated with that database. In this case principal_id is associated with sys.database_principals.

Read more on getting AD details for a logon and permissions

How to get the Active Directory groups membership for user with Powershell

How to Export Active Directory Group Members with Powershell Get-ADGroupMember 

 

 


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 find list of AD groups of a user from SQL Server


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