Display group membership (including nested groups) of a domain account using SQL Server

Goal

To obtain a list of groups that a domain account is member of.

My way of achieving it

Using SSMS and Ad Hoc Distributed Queries. In order to use AD Hoc Distributed queries temporary you must enable it using sp_configure.

Sometimes when we need to troubleshoot why a domain account doesn’t have the requested permissions we have to check his group membership. Most of the time the access to SQL server is given by making an user member of a specific active directory domain group and have that group added as a login or by adding that domain account as a login. Although most of the time this action will solve the request there might be cases when the users say that they don’t have the same rights as other users. This post address this situation by discovering the group membership of those domain accounts. Although we can use net user command with domain switch in a command prompt window

– net user domain_account /domain

the problem is that this will not return any nested groups.

When comparing 2 domain accounts for differences that they might have in SQL Server in regards to permissions we want to be sure that those accounts have the same group membership.

The output of the script is below and can be downloaded from here

groupmembership

 

One thought on “Display group membership (including nested groups) of a domain account using SQL Server”

Leave a Reply

Your email address will not be published. Required fields are marked *