The following PowerShell script finds all of the Active Directory group memberships for users in a target Active Directory Group. The PowerShell script is looking for 2 parameters:

  1. TARGET_AD_GROUP – The AD group in question
  2. $logfile – A text file destination, saved as a CSV

The script will first find all members of the TARGET_AD_GROUP. The script will then store the array of users in the variable $user. The script will then iterate over each of the users in the array. During each iteration, the script will find all groups that the current user is a member of. This resulting groups are stored in the array $groups. The script will then iterate over the groups and write out the current user and all of their group memberships to the logfile, one group at a time.

Import-Module ActiveDirectory
$users = get-adgroupmember "TARGET_AD_GROUP" | Select-Object  SamAccountName, Name | Sort-Object name 
$logfile = "C:\OutFile.csv"
add-content $logfile "AccountName,UserName,GroupName"

foreach($user in $users){
    $groups =GET-ADUser -Identity $user.SamAccountName  –Properties MemberOf | `
    Select-Object -ExpandProperty MemberOf | `
    Get-ADGroup -Properties name | `
    Sort-Object name | `
    Where-Object { $_.GroupCategory -eq "Security"}|`
    Where-Object {$_.name -like "*SQL*"}|` # An additional filter
    Select-Object name
    foreach($group in $groups){
        $outline = "{0},{1},{2}" -f $user.SamAccountName, $user.Name, $group.name
        add-content $logfile $outline
    }
}

The results will look something like this:

AccountName UserName GroupName
asmith Adam Smith TARGET_AD_GROUP
asmith Adam Smith SECURE_AD_GROUP
asmith Adam Smith OTHER_AD_GROUP
bjones Brad Jones TARGET_AD_GROUP
bjones Brad Jones MANAGER_AD_GROUP
bjones Brad Jones CALENDAR_AD_GROUP
kcarter Kelly Carter TARGET_AD_GROUP
kcarter Kelly Carter SR_MANAGER_AD_GROUP
kcarter Kelly Carter MANAGER_AD_GROUP
wwoods Wilhem Woods DBA_AD_GROUP

You can then import the results into SQL Server, Excel, etc for pivoting and further analysis.

Rudy

Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter