When it comes to user logins and permissions in SQL Server, I typically prefer to use Active Directory groups. The problem with that is that you don’t always know what groups a given user is in. I recently had a case where I needed to compare the Active Directory Groups of 2 users and determine which AD Groups both users were in. As it turns out, with a little prep work this is really easy to do in PowerShell.
The Prep
The easiest way to do this in PowerShell is with the ActiveDirectory module. The installation of the AD module varies significantly for the different Windows and PowerShell versions, but for Windows 8 and 10, you just have to install the Remote Server Administration Tools (RSAT) from Microsoft. Here is a really good walkthrough of the various installs.
Once you get RSAT installed, you’ll have to run the following command to import the AD module into PowerShell.
|
1 2 3 |
import-module activedirectory |
The Code
Once you have the AD module imported, you can call commands that interface directly with AD. Here are some of the simple ones:
Check an AD User account
|
1 2 3 |
Get-ADUser 'Homer.Simpson' |
Check an AD Group
|
1 2 3 |
Get-ADGroupMember -Identity 'the-simpsons' |
In my particular case, I needed to find out which groups were shared by 2 particular users. I was easily able to do this by storing the results of Get-ADPrincipalGroupMembership for each user, and then using Compare-Object to show which AD Groups matched.
Compare Users’ AD Groups
|
1 2 3 4 5 6 7 8 9 10 11 |
$user1 = 'Homer.Simpson' $user2 = 'Barney.Gumble' $u1g = Get-ADPrincipalGroupMembership $user1 | select name $u2g = Get-ADPrincipalGroupMembership $user2 | select name Compare-Object -ReferenceObject $u1g -DifferenceObject $u2g -IncludeEqual -PassThru -ExcludeDifferent | SELECT Name |
As it turns out, for those that are curious, “Moe’s Tavern” was the only group these 2 users shared.
Pingback: Comparing Users’ Active Directory Group Membership – Curated SQL