Login Resource

Synopsis

Manages SQL Server logins, including SQL authentication, Windows authentication, password policies, and server role membership.

Type

OpenDsc.SqlServer/Login

Capabilities

• Get
• Set
• Delete
• Export

Properties

Connection properties

serverInstance

SQL Server instance name. Use . or (local) for the default instance, or server\instance for named instances.

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

connectUsername

Username for SQL authentication. Omit for Windows 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

Login properties

name

Name of the login.

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

loginType

Login type: SqlLogin, WindowsUser, WindowsGroup, Certificate, AsymmetricKey, ExternalUser, or ExternalGroup.

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

password

Password. Required when creating SQL logins.

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

defaultDatabase

Default database for the login.

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

language

Default language.

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

disabled

Whether the login is disabled.

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

passwordExpirationEnabled

Whether password expiration policy is enforced.

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

passwordPolicyEnforced

Whether password policy is enforced.

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

mustChangePassword

Whether the user must change the password at next login.

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

denyWindowsLogin

Whether to deny Windows login access. Only applies to Windows logins.

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

serverRoles

Server roles to assign. Values must be unique.

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

_purge

When true, removes roles not in serverRoles. When false, only adds roles.

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

Read-only properties

createDate

Creation date of the login.

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

dateLastModified

Date the login was last modified.

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

hasAccess

Whether the login has server access.

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

isLocked

Whether the login is locked out.

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

isPasswordExpired

Whether the password has expired.

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

isSystemObject

Whether this is a system login.

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

DSC properties

_exist

Whether the login should exist. Defaults to true.

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

Examples

Example 1 — Get a login

PowerShellShell

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

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

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

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

Example 2 — Create a SQL login

PowerShellShell

$resourceInput = @'
serverInstance: .
name: AppUser
loginType: SqlLogin
password: 'P@ssw0rd!'
defaultDatabase: AppDb
passwordPolicyEnforced: true
serverRoles:
  - public
'@

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

resource_input=$(cat <<'EOF'
serverInstance: .
name: AppUser
loginType: SqlLogin
password: 'P@ssw0rd!'
defaultDatabase: AppDb
passwordPolicyEnforced: true
serverRoles:
  - public
EOF
)

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

Example 3 — Delete a login

PowerShellShell

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

dsc resource delete -r OpenDsc.SqlServer/Login --input $resourceInput

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

dsc resource delete -r OpenDsc.SqlServer/Login --input "$resource_input"

Example 4 — Configuration document

$schema: https://aka.ms/dsc/schemas/v3/bundled/config/document.json
resources:
  - name: Application login
    type: OpenDsc.SqlServer/Login
    properties:
      serverInstance: "."
      name: AppUser
      loginType: SqlLogin
      password: "[parameter('appUserPassword')]"
      defaultDatabase: AppDb
      passwordPolicyEnforced: true
      passwordExpirationEnabled: true
      serverRoles:
        - public

Exit codes

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