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
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
