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.

Outputs

Microsoft.SqlServer.Management.Smo.DatabaseRole

Returns one DatabaseRole object for each role created. One role is created per Role parameter value in each target database.

Default display properties (via Select-DefaultView):

• ComputerName: The computer name of the SQL Server instance
• InstanceName: The SQL Server instance name
• SqlInstance: The full SQL Server instance name (computer\instance)
• Name: The name of the newly created database role
• Parent: The name of the database containing the role
• Owner: The database principal that owns the role (dbo by default, or custom owner if specified)

Additional properties available (from SMO DatabaseRole object):

• ID: Unique identifier for the role
• CreateDate: DateTime when the role was created
• DateLastModified: DateTime when the role was last modified All properties from the base SMO DatabaseRole object are accessible using Select-Object *.