Getting the AD Groups a User Belongs In To Grant Database Access

We usually get requests that ask for database permissions that you pattern with another user's permissions.  This is not the ideal scenario.  What should happen, is they need to give what type of permissions they need or they should provide the AD groups they need to be added to.  But from years of working as a DBA, we still get requests of this nature.

So, annoyed this morning, I wrote a PowerShell script to grab all the AD groups a login belongs to, compare it to the AD groups defined in the database, and return the intersection.   

Here's the script, I hope it helps someone.

Clear-Host

Clear-History

$userID = Read-Host -prompt "Username "

$serverName = Read-Host -prompt "Server Name "

$databaseName = Read-Host -prompt "Database Name "

$UserGroups = Get-ADPrincipalGroupMembership -Identity $UserID | Select-Object -ExpandProperty Name

$buffer1 = $UserGroups -join "', '"

$GroupsString = "'$buffer1'"

if ($serverName -Match ".database.windows.net") {

    $strQueryAD = "SELECT name AS [Group Name] FROM sys.database_principals WHERE type_desc IN ('EXTERNAL_GROUP') AND REPLACE(name,'$env:USERDOMAIN\','') IN ($GroupsString)"

    Connect-AzAccount -WarningAction Ignore | Out-Null

    $access_token = (Get-AzAccessToken -ResourceUrl "https://database.windows.net").Token

    Invoke-SqlCmd -ServerInstance $serverName -Database $databaseName -AccessToken $access_token -Query $strQueryAD -Verbose

}

else {

    $strQueryAD = "SELECT name AS [Group Name] FROM sys.database_principals WHERE type_desc IN ('WINDOWS_GROUP') AND REPLACE(name,'$env:USERDOMAIN\','') IN ($GroupsString)"

    Invoke-SqlCmd -ServerInstance $serverName -Database $databaseName -Query $strQueryAD -TrustServerCertificate -Verbose  

}