User and User Group Queries

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

Domain Level:
— 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’
REVERT

— Query the list of AD Groups that the user account belong to from Temp Table
SELECT name FROM #User

Server Level:
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.
EXEC master.dbo.xp_logininfo’MyDomain\MyGroup’,’members’

References:

http://sqlstudies.com/2014/12/29/finding-a-common-ad-group-for-a-set-of-users/
http://royalsql.com/2013/08/05/the-fairytale-girls-in-deep-distress-and-had-to-query-acitve-directory/

Advertisements

About SQLKit

For almost 10 years supporting SQL versions starting 6.5 to current as an production SQL DBA and as Application admin. Interests or learning include Hadoop , MySQL, PostGRESql and SAP BASIS administration.
This entry was posted in MSSQL, Permissions. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s