I get user requests all the time for access rights and i don’t have read access to AD to check groups to see if the user is already a member or not.
We can run the below scripts from SQL server Management Studio
— Collect AD Groups for “Domain Account” and stores it in a Temp table
EXECUTE AS LOGIN = ‘Domain Account’
SELECT name INTO #User from sys.login_token
WHERE TYPE = ‘WINDOWS GROUP’
— Query the list of AD Groups that the user account belong to from Temp Table
SELECT name FROM #User
The below just are localized to the server, the queries work just for Domain user or Group that have access to the SQL server.If you include the ones not on the server they dont return any results.
/*This query based on Domain account passed lists all of the groups it belongs to and kind of permissions they have including the Domain account itself listing with in that server*/
EXEC master.dbo.xp_logininfo’Domain User’,’all’
— This query lists all members in a group.