SqlDatabaseRole

SqlDatabaseRole

Parameters

Parameter	Attribute	DataType	Description	Allowed Values
InstanceName	Key	String	The name of the SQL instance to be configured.
DatabaseName	Key	String	The name of the database in which the role should be configured.
Name	Key	String	The name of the database role to be added or removed.
ServerName	Write	String	The host name of the SQL Server to be configured. Default value is $env:COMPUTERNAME.
Members	Write	StringArray[]	The members the database role should have. This parameter will replace all the current database role members with the specified members. Can only be used when parameter Ensure is set to 'Present'.
MembersToInclude	Write	StringArray[]	The members the database role should include. This parameter will only add members to a database role. Can only be used when parameter Ensure is set to 'Present'. Can not be used at the same time as parameter Members.
MembersToExclude	Write	StringArray[]	The members the database role should exclude. This parameter will only remove members from a database role. Can only be used when parameter Ensure is set to 'Present'. Can not be used at the same time as parameter Members.
MembersInDesiredState	Read	Boolean	Indicates whether the database role members are in the desired state.
Ensure	Write	String	If 'Present' (the default value) then the role will be added to the database and the role membership will be set. If 'Absent' then the role will be removed from the database.	Present, Absent

Description

The SqlDatabaseRole DSC resource is used to create a database role when Ensure is set to 'Present' or remove a database role when Ensure is set to 'Absent'. The resource also manages members in both built-in and user created database roles.

Requirements

• Target machine must be running Windows Server 2012 or later.
• Target machine must be running SQL Server Database Engine 2012 or later.

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 database roles named ReportEditor and ReportViewer are present in the AdventureWorks database on instance sqltest.company.local\DSC.

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

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlDatabaseRole 'ReportEditor_AddRole'
        {
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            DatabaseName         = 'AdventureWorks'
            Name                 = 'ReportEditor'
            Ensure               = 'Present'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlDatabaseRole 'ReportViewer_AddRole'
        {
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            DatabaseName         = 'AdventureWorks'
            Name                 = 'ReportViewer'
            Ensure               = 'Present'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

Example 2

This example shows how to ensure that the database role named ReportViewer is not present in the AdventureWorks database on instance sqltest.company.local\DSC.

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

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlDatabaseRole 'ReportViewer_DropRole'
        {
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            DatabaseName         = 'AdventureWorks'
            Name                 = 'ReportViewer'
            Ensure               = 'Absent'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

Example 3

This example shows how to do the following:

1. Ensure that the database role named ReportViewer is present in the AdventureWorks database on instance sqltest.company.local\DSC.
2. Ensure that users CONTOSO\Barbara and CONTOSO\Fred will always be the only members of the role.

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

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlDatabaseRole 'ReportViewer_EnforceRoleMembers'
        {
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            DatabaseName         = 'AdventureWorks'
            Name                 = 'ReportViewer'
            Members              = @('CONTOSO\Barbara', 'CONTOSO\Fred')
            Ensure               = 'Present'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

Example 4

This example shows how to do the following:

1. Ensure that the database role named ReportViewer is present in the AdventureWorks database on instance sqltest.company.local\DSC.
2. Ensure that users CONTOSO\Barbara and CONTOSO\Fred will always be members of the role.

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

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlDatabaseRole 'ReportViewer_IncludeRoleMembers'
        {
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            DatabaseName         = 'AdventureWorks'
            Name                 = 'ReportViewer'
            MembersToInclude     = @('CONTOSO\Barbara', 'CONTOSO\Fred')
            Ensure               = 'Present'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

Example 5

This example shows how to do the following:

1. Ensure that the database role named ReportViewer is present in the AdventureWorks database on instance sqltest.company.local\DSC.
2. Ensure that users CONTOSO\Barbara and CONTOSO\Fred will never be members of the role.

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

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlDatabaseRole 'ReportViewer_ExcludeRoleMembers'
        {
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            DatabaseName         = 'AdventureWorks'
            Name                 = 'ReportViewer'
            MembersToExclude     = @('CONTOSO\Barbara', 'CONTOSO\Fred')
            Ensure               = 'Present'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

Example 6

This example shows how to do the following:

1. Ensure that the database role named ReportViewer is present in the AdventureWorks database on instance sqltest.company.local\DSC.
2. Ensure that users CONTOSO\Barbara and CONTOSO\Fred will always be members of the role.
3. Ensure that the user CONSOSO\Intern1 will never be a member of the role.

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

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlDatabaseRole 'ReportViewer_IncludeAndExcludeRoleMembers'
        {
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            DatabaseName         = 'AdventureWorks'
            Name                 = 'ReportViewer'
            MembersToInclude     = @('CONTOSO\Barbara', 'CONTOSO\Fred')
            MembersToExclude     = @('CONTOSO\Intern1')
            Ensure               = 'Present'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}