Quick script to find all the objects (Users, Groups) in the *deny* roles in all databases. Handy for when your AD account setups are heavily nested with duplicate users in multiple conflicting groups . If you find yourself having to use deny in order to get permissions correct, it’s probably time you flattened out your groups a bit. Using DENY can be a chore to track down. This just spits out a gridview of the members of the *deny* roles.
Import-Module SqlPS -DisableNameChecking
$objects = @();
$servers = gc -Path c:\Servers.txt
try{
$Servers | %{
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $_
$srv.Databases | where{$_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal} | %{
$dbName = $_.Name
$_.Roles | where{$_.Name -like '*deny*'} | %{
$roleName = $_.Name
$roleMembers = $_.EnumMembers()
$roleMembers | %{
$objects += [PSCustomObject] @{
ServerName = $srvName
DatabaseName = $dbName
Role = $roleName
RoleMember = $_
}
}
}
}
}
$objects | Out-GridView
}
catch{
$_ | fl -Force
}
Sql Server Powershell 
Leave a comment