Remove-DbaDbRole

Synopsis

Removes custom database roles from SQL Server databases

Description

Removes user-defined database roles from SQL Server databases while protecting against accidental deletion of system roles and intelligently handling schema ownership. This function automatically excludes fixed database roles (like db_owner, db_datareader) and the public role, ensuring only custom roles created for specific security requirements can be removed.

When a role owns schemas, the function intelligently manages the cleanup: schemas with the same name as the role are dropped (if empty), while other owned schemas have their ownership transferred to ‘dbo’. If schemas contain objects, use -Force to allow ownership transfer and proceed with role removal.

You can target specific roles across multiple databases and instances, making it ideal for standardizing security configurations or bulk cleanup operations. By default, system databases are excluded unless explicitly included with the IncludeSystemDbs parameter.

Syntax

Remove-DbaDbRole
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String[]>]
    [[-ExcludeDatabase] <String[]>]
    [[-Role] <String[]>]
    [[-ExcludeRole] <String[]>]
    [-IncludeSystemDbs]
    [[-InputObject] <Object[]>]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Remove-DbaDbRole -SqlInstance localhost -Database dbname -Role "customrole1", "customrole2"

Removes roles customrole1 and customrole2 from the database dbname on the local default SQL Server instance

Example: 2

PS C:\> Remove-DbaDbRole -SqlInstance localhost, sql2016 -Database db1, db2 -Role role1, role2, role3

Removes role1,role2,role3 from db1 and db2 on the local and sql2016 SQL Server instances

Example: 3

PS C:\> $servers = Get-Content C:\servers.txt
PS C:\> $servers | Remove-DbaDbRole -Database db1, db2 -Role role1

Removes role1 from db1 and db2 on the servers in C:\servers.txt

Example: 4

PS C:\> $roles = Get-DbaDbRole -SqlInstance localhost, sql2016 -Database db1, db2 -Role role1, role2, role3
PS C:\> $roles | Remove-DbaDbRole

Removes role1,role2,role3 from db1 and db2 on the local and sql2016 SQL Server instances

Optional Parameters

-SqlInstance

The target SQL Server instance or instances. This can be a collection and receive pipeline input to allow the function to be executed against multiple SQL Server instances.

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-Database

Specifies which databases to remove roles from. Accepts multiple database names and supports wildcards for pattern matching.
When omitted, the function processes all user databases on the instance. Use this when you need to clean up roles from specific databases only.

-ExcludeDatabase

Excludes specific databases from role removal operations. Accepts multiple database names and supports wildcards.
Use this when processing all databases except certain ones, such as excluding production databases during cleanup operations.

-Role

Specifies which custom database roles to remove. Accepts multiple role names and supports wildcards for pattern matching.
When omitted, all custom roles in the target databases will be removed. Fixed database roles (db_owner, db_datareader, etc.) and the public role are automatically protected from deletion.

-ExcludeRole

Excludes specific roles from removal operations. Accepts multiple role names and supports wildcards.
Use this when you want to remove most custom roles but preserve certain ones, such as keeping application-specific roles while cleaning up deprecated security configurations.

-IncludeSystemDbs

Allows role removal operations to target system databases (master, model, msdb, tempdb).
By default, system databases are excluded to prevent accidental removal of roles that may be required for SQL Server operations. Only use this when you specifically need to clean up custom roles from
system databases.

-InputObject

Accepts piped objects from Get-DbaDbRole, Get-DbaDatabase, or SQL Server instances for processing.
Use this for pipeline operations where you first retrieve specific roles or databases, then remove roles from them. This allows for more complex filtering and processing scenarios.

-Force

Forces schema ownership transfer to ‘dbo’ when the role owns schemas containing database objects. Without this, role removal fails if owned schemas contain objects.
Use this during role cleanup when you need to ensure complete removal regardless of schema dependencies.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.

-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

-Confirm

Prompts you for confirmation before executing any changing operations within the command.

Outputs

None

This function performs role removal operations but does not return any output objects. Success or failure information is provided through verbose messages and warning messages during execution.