Database User Resource

Synopsis

Manages SQL Server database users, including SQL users mapped to logins, Windows users, contained database users, and certificate or asymmetric key mapped users.

Type

OpenDsc.SqlServer/DatabaseUser

Capabilities

• Get
• Set
• Delete
• Export

Properties

serverInstance

SQL Server instance name.

Type: string
Required: No
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 user.

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

name

Name of the database user.

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

userType

User type. See User types below.

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

login

Login mapped to this user. Required for SqlUser.

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

defaultSchema

Default schema for the user.

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

password

Password for contained database users.

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

asymmetricKey

Asymmetric key name. Used for AsymmetricKeyMappedUser.

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

certificate

Certificate name. Used for CertificateMappedUser.

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

defaultLanguage

Default language.

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

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

hasDBAccess

Whether the user has database access.

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

isSystemObject

Whether this is a system user.

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

sid

Security identifier (SID).

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

authenticationType

Authentication type.

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

_exist

Whether the user should exist.

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

User types

Value	Description
SqlUser	SQL user mapped to a server login
NoLogin	User without a login
WindowsUser	Windows user
WindowsGroup	Windows group
CertificateMappedUser	User mapped to a certificate
AsymmetricKeyMappedUser	User mapped to an asymmetric key
ExternalUser	External user (Microsoft Entra ID)
ExternalGroup	External group (Microsoft Entra ID)

Examples

Example 1 — Get a database user

PowerShellShell

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

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

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

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

Example 2 — Create a user mapped to a login

PowerShellShell

$resourceInput = @'
serverInstance: .
databaseName: AppDb
name: AppUser
userType: SqlUser
login: AppUser
defaultSchema: dbo
'@

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

resource_input=$(cat <<'EOF'
serverInstance: .
databaseName: AppDb
name: AppUser
userType: SqlUser
login: AppUser
defaultSchema: dbo
EOF
)

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

Example 3 — Configuration document

$schema: https://aka.ms/dsc/schemas/v3/bundled/config/document.json
resources:
  - name: Application database user
    type: OpenDsc.SqlServer/DatabaseUser
    properties:
      serverInstance: "."
      databaseName: AppDb
      name: AppUser
      userType: SqlUser
      login: AppUser
      defaultSchema: dbo

Exit codes

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