Finding Orphaned Groups

2 minute read

Finding Orphaned Groups

Active Directory groups are one of the easiest ways to allow your organization to have a separation of duties between Database Administrators and Identity and Access Management professionals. You can create an Active Directory group, create a login on the server, create a user in the database mapped to the login, and grant access to the user (which is actually a group). Then whenever people need to have access granted or revoked the Identity and Access Management professionals can handle that by just adding the actual users to the group.

The one obvious downfall to this setup is the additional management when setting up new databases or decommissioning old ones. Remembering to decommission old groups after I decommission a database is an area I struggle with. Sounds like a perfect solution for a script!

TLDR

You must have the dbatools module installed.

Grab my orphaned groups function from GitHub. Disclaimer: You should always review code yourself before running code on your computer.

. { iwr -useb https://raw.githubusercontent.com/awickham10/powershell/master/Security/Get-DbaOrphanedSecurityGroup.ps1 } | iex

Get-Help Get-DbaOrphanedSecurityGroup -Full

The Details

This script goes through several different steps in order to do the comparison. Here’s a high level overview. We’ll then dive into more details later on.

Get a list of servers from a parameter or from Central Management Servers depending on which parameter set was used. Getting the list from Central Management Servers is easy using Get-DbaRegisteredServer from the dbatools module.

    $servers = switch ($PSCmdlet.ParameterSetName) {
        'Server' {
            dbatools\Connect-DbaInstance -SqlInstance $SqlInstance -SqlCredential $SqlCredential
        }
        'CMS' {
            dbatools\Get-DbaRegisteredServer -SqlInstance $CmsServer -SqlCredential $SqlCredential | Where-Object {
                $_.Name -notin $ExcludeSqlInstance
            }
        }
   }

Loop through the servers and get a list of Windows Group logins defined on the. Make sure to filter the logins by GroupFilter. The filter is dynamically built in order to support multiple GroupFilters being passed in (-like doesn’t support comparing against an array).

foreach ($server in $servers) {
    Write-Verbose "Getting groups from $($server.Name)"

    # default filter
    $filterBlockString = "
        `$groupParts = `$_.Name.Split('\')
        `$_.LoginType -eq 'WindowsGroup' -and `$groupParts[0] -notin (`$_.ComputerName, 'BUILTIN', 'NT SERVICE') -and ("

    # add in GroupFilter
    $filterBlockParts = @()
    foreach ($groupFilterParam in $GroupFilter) {
        $filterBlockParts += "`$groupParts[1] -like '$groupFilterParam'"
    }
    $filterBlockString += ($filterBlockParts -join ' -or ') + ')'

    $filterBlock = [ScriptBlock]::Create($filterBlockString)

    # get groups for the instance
    $inUseGroups += dbatools\Get-DbaLogin -SqlInstance $server.Name -SqlCredential $SqlCredential | Where-Object $filterBlock | Select-Object -Property @(
        @{ Name = 'Name'; Expression = { $_.Name.Split('\')[1] }}
    )
}
$inUseGroups = $inUseGroups | Select-Object -ExpandProperty Name -Unique

Get a list of groups from Active Directory matching the GroupFilter parameter. I initially used LDAPFilter because it used to provide significant performance benefits but after some testing the difference between Filter and LDAPFilter was inconclusive.

# get groups in AD
$filter = @()
foreach ($filterItem in $GroupFilter) {
    $filter += "SamAccountName -like '$filterItem'"
}
$filterString = $filter -join ' -or '

$adGroups = Get-AdGroup -Filter $filterString | Select-Object -ExpandProperty Name

Compare the list of groups and return the results using Compare-Object. If you’re looking at a huge amount of groups, this may need to be sorted first, but I didn’t seem to have any issues with almost 750 groups.

if ($inUseGroups -and $adGroups) {
    $compare = Compare-Object $inUseGroups $adGroups

    foreach ($group in $compare) {
        [PSCustomObject]@{
            'Name'              = $group.InputObject
            'InSql'             = $group.SideIndicator -eq '<='
            'InActiveDirectory' = $group.SideIndicator -eq '=>'
        }
    }
}

You’ll also notice something nice in this script, no T-SQL involved. Shout out to dbatools.io for the awesome module their contributors have put together.

Leave a Comment