Get Objects in the *deny* roles in Sql Server via Powershell

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


    $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
    $_ | fl -Force

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.