New-DbaDbRole

Synopsis

Creates new database roles in one or more SQL Server databases.

Description

Creates custom database roles for implementing role-based security in SQL Server databases. This function handles the creation of user-defined database roles that can later be granted specific permissions and have users or other roles assigned to them. You can create the same role across multiple databases for consistency, and optionally specify a custom owner instead of the default dbo. This eliminates the need to manually create roles through SSMS or T-SQL for each database.

Syntax

New-DbaDbRole
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String[]>]
    [[-ExcludeDatabase] <String[]>]
    [[-Role] <String[]>]
    [[-Owner] <String>]
    [[-InputObject] <Database[]>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> New-DbaDbRole -SqlInstance sql2017a -Database db1 -Role 'dbExecuter'

Will create a new role named dbExecuter within db1 on sql2017a instance.

Optional Parameters

-SqlInstance

The target SQL Server instance or 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 create the new role(s) in. Accepts wildcards for pattern matching.
Use this when you need to create roles in specific databases instead of all databases on the instance.
If unspecified, the role will be created in all accessible databases.

-ExcludeDatabase

Specifies databases to exclude from role creation when processing all databases.
Use this to skip system databases or specific user databases where the role shouldn’t be created.
Particularly useful when creating standardized roles across most but not all databases.

-Role

Specifies the name(s) of the custom database role(s) to create.
Use meaningful names that reflect the role’s intended permissions like ‘AppReadOnly’ or ‘ReportUsers’.
The function will create each specified role in all target databases.

-Owner

Specifies the database principal that will own the new role. Defaults to ‘dbo’ if not specified.
Use this when you need a specific user or role to own the new database role for security or organizational requirements.
The owner must exist in each target database.

-InputObject

Accepts database objects piped from Get-DbaDatabase for role creation.
Use this for advanced filtering or when working with databases from multiple instances.
This parameter allows you to chain Get-DbaDatabase with specific filters before creating roles.

-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.