Remove-DbaDbRole should be able to reassign schema ownership like Remove-DbaDbUser

[rickloveslamp]

Summarize Functionality

Right now if you try to remove a database role that owns a schema, it will just skip it/fail. This is different than how Remove-DbaDbUser handles the same situation. That command will remove a schema with the same name as the user or reassign ownership to dbo. The -Force parameter can also be specified if there are objects in the schema. Remove-DbaDbRole could have similar logic to drop the schema or change the schema owners.

Is there a command that is similiar or close to what you are looking for?

Yes

Technical Details

Remove-DbaDbUser uses the EnumOwnedObjects() method that does not exist on role objects. The structure of each is also a little different, so it's not as simple as copying over the relevant section. But the sample code below covers most of the functionality I think (not tested!)

foreach ($dbRole in $dbRoles) {
    $db = $dbRole.Parent
    $instance = $db.Parent
    $ownedObjects = $false

    if ($db.IsSystemObject -and  (!$IncludeSystemDbs )) {
        Write-Message -Level Verbose -Message "Can only remove roles from System database when IncludeSystemDbs switch used."
        continue
    }
    if ($dbRole.IsFixedRole -or $dbRole.Name -eq 'public'){
        Write-Message -Level Verbose -Message "Cannot remove fixed role $dbRole from database $db on instance $instance"
        continue
    }

    $ownedSchemas = $db.Schemas | Where-Object { $_.Owner -eq $dbRole.Name }
    foreach ($schema in $ownedSchemas) {
        $ownedUrns = $schema.EnumOwnedObjects()
        if ($schema.Name -eq $dbRole.Name) {
            if ($ownedUrns){
                Write-Message -Level Warning -Message "Role $($dbRole.Name) owns the Schema $($schema.Name), which owns $($ownedUrns.Count) object(s). If you want to change the schema's owner to [dbo] and drop the role anyway, use -Force parameter. Role $(dbRole.Name) will not be removed."
                $ownedObjects = $true              
            } else {
                if ($PSCmdlet.ShouldProcess($instance, "Drop Schema $schema from Database $db.")) {
                    $schema.Drop()
                }
            }
        } else {
            if ($ownedUrns -and (!$Force)) {
                Write-Message -Level Warning -Message "Role $($dbRole.Name) owns the Schema $($schema.Name), which owns $($ownedUrns.Count) object(s). If you want to change the schema's owner to [dbo] and drop the role anyway, use -Force parameter. Role $(dbRole.Name) will not be removed."
                $ownedObjects = $true              
            } else {
                Write-Message -Level Verbose -Message "Owner of Schema $schema will be changed to [dbo]."
                if ($PSCmdlet.ShouldProcess($server, "Change the owner of Schema $schema to [dbo].")) {
                    $schema.Owner = "dbo"
                    $schema.Alter()
                }
            }
        }
    }

    if (!($ownedObjects)) {
        if ($PSCmdlet.ShouldProcess($instance, "Remove role $dbRole from database $db")) {
            $dbRole.Drop()
        }
    } else {
        Write-Message -Level Warning -Message "Could not remove role $dbRole because it still owns one or more schemas."
    }
}