Expand Windows Groups on Sql Server

Quick post on how to expand windows groups to show their sub-groups and logins in sql server.  This just writes the group hierarchy to the console.  Use at your own risk.

import-module activedirectory

function Get-GroupHierarchy{
    param(
        [Parameter(Mandatory=$true)]
        [String]$searchGroup
    )
    $outputs = @();
    [int]$i++ | out-null;

    get-adgroupmember $searchGroup | sort-object objectClass -descending | %{
        $output = new-object -TypeName PSObject -Property @{
            Parent = $searchGroup
            GroupName = $_.Name
            Type = $_.objectClass
            Hierarchy = $i
        }
        $outputs += $output

        if($_.ObjectClass -eq 'group'){
            $outputs += Get-GroupHierarchy $_.name
        }
    }
    return $outputs;
}

cls
$srvName = 'ServerName'
$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
$srvConn.ServerInstance = $srvName
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
$ignoreGroups = @('NT SERVICE\MSSQLSERVER', 'NT SERVICE\SQLSERVERAGENT');

$srv.Logins | where{$_.LoginType -eq [Microsoft.SqlServer.Management.Smo.LoginType]::WindowsGroup -and $_.Name -notin $ignoreGroups} | %{
    $loginName = $_.Name.Replace('TRX0\', '')
    Write-Host "Windows Group:  $loginName" -ForegroundColor Green
    Get-GroupHierarchy $loginName | ft -AutoSize   
    Write-Host "`n`r"
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: