Skip to content

Database Role Resource

Synopsis

Manages SQL Server database roles, including role creation, ownership, and member management with additive or exact (_purge) modes.

Type

OpenDsc.SqlServer/DatabaseRole

Capabilities

  • Get
  • Set
  • Delete
  • Export

Properties

serverInstance

SQL Server instance name.

Type: string
Required: Yes
Access: Read/Write
Default value: None

connectUsername

Username for SQL authentication.

Type: string
Required: No
Access: Write-Only
Default value: None

connectPassword

Password for SQL authentication.

Type: string
Required: No
Access: Write-Only
Default value: None

databaseName

Name of the database containing the role.

Type: string
Required: Yes
Access: Read/Write
Default value: None

name

Name of the database role.

Type: string
Required: Yes
Access: Read/Write
Default value: None

owner

Owner of the role (user or role).

Type: string
Required: No
Access: Read/Write
Default value: None

members

Members of the role. Values must be unique.

Type: string[]
Required: No
Access: Read/Write
Default value: None

_purge

When true, removes members not in the list. When false (default), only adds members.

Type: bool
Required: No
Access: Write-Only
Default value: false

createDate

Creation date.

Type: datetime
Required: No
Access: Read-Only
Default value: None

dateLastModified

Date last modified.

Type: datetime
Required: No
Access: Read-Only
Default value: None

isFixedRole

Whether this is a fixed database role.

Type: bool
Required: No
Access: Read-Only
Default value: None

_exist

Whether the role should exist.

Type: bool
Required: No
Access: Read/Write
Default value: true

Examples

Example 1 — Get a role

$resourceInput = @'
serverInstance: .
databaseName: AppDb
name: db_datareader
'@

dsc resource get -r OpenDsc.SqlServer/DatabaseRole --input $resourceInput

resource_input=$(cat <<'EOF'
serverInstance: .
databaseName: AppDb
name: db_datareader
EOF
)

dsc resource get -r OpenDsc.SqlServer/DatabaseRole --input "$resource_input"

Example 2 — Create a role with members

$resourceInput = @'
serverInstance: .
databaseName: AppDb
name: AppReaders
members:
  - AppUser
  - ReportUser
'@

dsc resource set -r OpenDsc.SqlServer/DatabaseRole --input $resourceInput

resource_input=$(cat <<'EOF'
serverInstance: .
databaseName: AppDb
name: AppReaders
members:
  - AppUser
  - ReportUser
EOF
)

dsc resource set -r OpenDsc.SqlServer/DatabaseRole --input "$resource_input"

Example 3 — Configuration document

$schema: https://aka.ms/dsc/schemas/v3/bundled/config/document.json
resources:
  - name: Application reader role
    type: OpenDsc.SqlServer/DatabaseRole
    properties:
      serverInstance: "."
      databaseName: AppDb
      name: AppReaders
      members:
        - AppUser
        - ReportUser
      _purge: true

Exit codes

Code Description
0 Success
1 Error
2 Invalid JSON
3 Invalid argument
4 Unauthorized access
5 Invalid operation