Compare AD group membership of two users

Our Windows security setup is completely (well, mostly 🙂 ) AD groups based. When new employees are hired, we ask for an existing employee after whom the new employees permissions should be mirrored. In other words, if we added the new employee to the same AD groups (that are related to SQL Server of course), then the new user will be all set. BTW, if you are still managing any permissions on Windows (including SQL Server) on an individual user basis, you are doing it wrong.

Before we begin, if you are running Windows 7 or if you do not have the Active Directory module installed, please do so first by downloading and installing “Remote Server Administration Tools”. I personally cannot live without this module.

Let us call the new employee as NEW_USER and the existing employee as OLD_USER. Here is how to use PowerShell to do the comparison.

Get the AD groups that NEW_USER and OLD_USER belong to

$rslt1 = Get-ADPrincipalGroupMembership 'NEW_USER'
$rslt2 = Get-ADPrincipalGroupMembership 'OLD_USER'

If the user is really new, then he/she may not belong to any groups besides the basic ones.

Now to get the difference, let us just use Compare-Object

Compare-Object `
       -ReferenceObject ($rslt1 | select -ExpandProperty name) `
       -DifferenceObject ($rslt2 | select -ExpandProperty name)

InputObject SideIndicator
----------- -------------
GRP_PRD_MYAPP_USERS =>
GRP_SIRI_MYAPP_USERS =>
GROUP DUBLIN <=
GROUP PARIS <=
GROUP SPAIN <=
GROUP-EXTERNAL <=
EXCHANGE <= 

Above, we see the the differences between the two. Note that the column “SideIndicator” shows the group that one belongs to that the other does not and vice versa with either “=>” or “<=”.

If you wanted to limit the differences to a specific AD GroupCategory like “Security” or “Distribution” you could refine your comparison further

Compare-Object `
       -ReferenceObject ($rslt1 |
             where-object {$_.GroupCategory -eq 'Security'} |
             select -ExpandProperty name) `
       -DifferenceObject ($rslt2 |
             where-object {$_.GroupCategory -eq 'Security'} |
             select -ExpandProperty name) `

To also include the AD groups that both users are in (rather than just the differences), just use the “-IncludeEqual”switch in Compare-Object.

So, there you have it. It is just as easy to turn it into a function! I will leave it up to you to expand functionality to automatically sync. group membership and report on what changes were made so that the results can be saved somewhere for audit. I have to admit that that no two admins will handle this the same way!