SqlServerLogin

Parameters

Parameter	Attribute	DataType	Description	Allowed Values
Name	Key	String	The name of the login.
InstanceName	Key	String	Name of the SQL instance to be configured.
Ensure	Write	String	The specified login is Present or Absent. Default is Present.	Present, Absent
LoginType	Write	String	The type of login to be created. If LoginType is 'WindowsUser' or 'WindowsGroup' then provide the name in the format DOMAIN
ame. Default is WindowsUser. Unsupported login types are Certificate, AsymmetricKey, ExternalUser, and ExternalGroup.	WindowsUser, WindowsGroup, SqlLogin, Certificate, AsymmetricKey, ExternalUser, ExternalGroup
ServerName	Write	String	The hostname of the SQL Server to be configured. Default value is $env:COMPUTERNAME.
LoginCredential	Write	PSCredential	If LoginType is 'SqlLogin' then a PSCredential is needed for the password to the login.
LoginMustChangePassword	Write	Boolean	Specifies if the login is required to have its password change on the next login. Only applies to SQL Logins. Default is $true.
LoginPasswordExpirationEnabled	Write	Boolean	Specifies if the login password is required to expire in accordance to the operating system security policy. Only applies to SQL Logins. Default is $true.
LoginPasswordPolicyEnforced	Write	Boolean	Specifies if the login password is required to conform to the password policy specified in the system security policy. Only applies to SQL Logins. Default is $true.
Disabled	Write	Boolean	Specifies if the login is disabled. Default is $false.
DefaultDatabase	Write	String	Default database name.

Description

The SqlLogin DSC resource manages SQL Server logins for a SQL Server instance.

Requirements

• Target machine must be running Windows Server 2012 or later.
• Target machine must be running SQL Server Database Engine 2012 or later.
• When the LoginType 'SqlLogin' is used, then the login authentication mode must have been set to Mixed or Normal. If set to Integrated and error will be thrown.

Known issues

All issues are not listed here, see here for all open issues.

Examples

Example 1

This example shows how to ensure that the Windows user 'CONTOSO\WindowsUser', Windows group 'CONTOSO\WindowsGroup', and the SQL Login 'SqlLogin' exists.

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential,

        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $LoginCredential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlServerLogin 'Add_WindowsUser'
        {
            Ensure               = 'Present'
            Name                 = 'CONTOSO\WindowsUser'
            LoginType            = 'WindowsUser'
            ServerName           = 'TestServer.company.local'
            InstanceName         = 'DSC'
            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlServerLogin 'Add_DisabledWindowsUser'
        {
            Ensure               = 'Present'
            Name                 = 'CONTOSO\WindowsUser2'
            LoginType            = 'WindowsUser'
            ServerName           = 'TestServer.company.local'
            InstanceName         = 'DSC'
            PsDscRunAsCredential = $SqlAdministratorCredential
            Disabled             = $true
        }

        SqlServerLogin 'Add_WindowsUser_Set_Default_Database'
        {
            Ensure               = 'Present'
            Name                 = 'CONTOSO\WindowsUser3'
            LoginType            = 'WindowsUser'
            ServerName           = 'TestServer.company.local'
            InstanceName         = 'DSC'
            DefaultDatabase      = 'contoso'
            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlServerLogin 'Add_WindowsGroup'
        {
            Ensure               = 'Present'
            Name                 = 'CONTOSO\WindowsGroup'
            LoginType            = 'WindowsGroup'
            ServerName           = 'TestServer.company.local'
            InstanceName         = 'DSC'
            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlServerLogin 'Add_SqlLogin'
        {
            Ensure                         = 'Present'
            Name                           = 'SqlLogin'
            LoginType                      = 'SqlLogin'
            ServerName                     = 'TestServer.company.local'
            InstanceName                   = 'DSC'
            LoginCredential                = $LoginCredential
            LoginMustChangePassword        = $false
            LoginPasswordExpirationEnabled = $true
            LoginPasswordPolicyEnforced    = $true
            PsDscRunAsCredential           = $SqlAdministratorCredential
        }
    }
}

Example 2

This example shows how to remove the Windows user 'CONTOSO\WindowsUser', Windows group 'CONTOSO\WindowsGroup', and the SQL Login 'SqlLogin'.

Configuration Example
{
    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlServerLogin 'Remove_WindowsUser'
        {
            Ensure       = 'Absent'
            Name         = 'CONTOSO\WindowsUser'
            LoginType    = 'WindowsUser'
            ServerName   = 'TestServer.company.local'
            InstanceName = 'DSC'
        }

        SqlServerLogin 'Remove_WindowsGroup'
        {
            Ensure       = 'Absent'
            Name         = 'CONTOSO\WindowsGroup'
            LoginType    = 'WindowsGroup'
            ServerName   = 'TestServer.company.local'
            InstanceName = 'DSC'
        }

        SqlServerLogin 'Remove_SqlLogin'
        {
            Ensure       = 'Absent'
            Name         = 'SqlLogin'
            LoginType    = 'SqlLogin'
            ServerName   = 'TestServer.company.local'
            InstanceName = 'DSC'
        }
    }
}