Server Role Resource

Synopsis

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

Type

OpenDsc.SqlServer/ServerRole

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

name

Name of the server role.

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

owner

Owner of the role (login or role).

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

members

Members (logins or roles). 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

dateCreated

Creation date.

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

dateModified

Date last modified.

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

isFixedRole

Whether this is a fixed server 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 server role

PowerShellShell

$resourceInput = @'
serverInstance: .
name: sysadmin
'@

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

resource_input=$(cat <<'EOF'
serverInstance: .
name: sysadmin
EOF
)

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

Example 2 — Create a custom server role

PowerShellShell

$resourceInput = @'
serverInstance: .
name: AppAdmins
members:
  - AppLoginAdmin
  - AppLoginDBA
'@

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

resource_input=$(cat <<'EOF'
serverInstance: .
name: AppAdmins
members:
  - AppLoginAdmin
  - AppLoginDBA
EOF
)

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

Example 3 — Configuration document

$schema: https://aka.ms/dsc/schemas/v3/bundled/config/document.json
resources:
  - name: Application admin role
    type: OpenDsc.SqlServer/ServerRole
    properties:
      serverInstance: "."
      name: AppAdmins
      members:
        - AppLoginAdmin
        - AppLoginDBA
      _purge: true

Exit codes

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