Skip to content

Database Permission Resource

Synopsis

Manages SQL Server database-level permissions for users and database roles. Supports Grant, Grant With Grant, and Deny states.

Type

OpenDsc.SqlServer/DatabasePermission

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.

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

principal

Name of the principal (user or database role).

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

permission

Database permission (e.g., Connect, Select, Execute, Alter).

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

state

Permission state. Accepts Grant, GrantWithGrant, or Deny.

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

grantor

Grantor of the permission.

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

_exist

Whether the permission should exist.

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

Examples

Example 1 — Grant SELECT to a user

$resourceInput = @'
serverInstance: .
databaseName: AppDb
principal: AppUser
permission: Select
state: Grant
'@

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

resource_input=$(cat <<'EOF'
serverInstance: .
databaseName: AppDb
principal: AppUser
permission: Select
state: Grant
EOF
)

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

Example 2 — Configuration document

$schema: https://aka.ms/dsc/schemas/v3/bundled/config/document.json
resources:
  - name: Grant database connect
    type: OpenDsc.SqlServer/DatabasePermission
    properties:
      serverInstance: "."
      databaseName: AppDb
      principal: AppUser
      permission: Connect
      state: Grant

  - name: Grant database select
    type: OpenDsc.SqlServer/DatabasePermission
    properties:
      serverInstance: "."
      databaseName: AppDb
      principal: AppUser
      permission: Select
      state: Grant

Exit codes

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