SqlServerDsc

14.2.1

DSCResources/DSC_SqlDatabaseRole/en-US/about_SqlDatabaseRole.help.txt

.NAME
    SqlDatabaseRole
 
.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](https://github.com/dsccommunity/SqlServerDsc/issues?q=is%3Aissue+is%3Aopen+in%3Atitle+SqlDatabaseRole).
 
.PARAMETER InstanceName
    Key - String
    The name of the SQL Server instance to be configured.
 
.PARAMETER DatabaseName
    Key - String
    The name of the database in which the role should be configured.
 
.PARAMETER Name
    Key - String
    The name of the database role to be added or removed.
 
.PARAMETER ServerName
    Write - String
    The host name of the SQL Server to be configured. Default value is $env:COMPUTERNAME.
 
.PARAMETER Members
    Write - StringArray
    The members the database role should have. This parameter will replace all the current database role members with the specified members. Will only be used when parameter Ensure is set to 'Present'.
 
.PARAMETER MembersToInclude
    Write - StringArray
    The members the database role should include. This parameter will only add members to a database role. Will only be used when parameter Ensure is set to 'Present'. Can not be used at the same time as parameter Members.
 
.PARAMETER MembersToExclude
    Write - StringArray
    The members the database role should exclude. This parameter will only remove members from a database role. Will only be used when parameter Ensure is set to 'Present'. Can not be used at the same time as parameter Members.
 
.PARAMETER Ensure
    Write - String
    Allowed values: Present, Absent
    If 'Present' 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. Default value is 'Present'.
 
.PARAMETER MembersInDesiredState
    Read - Boolean
    Returns whether the database role members are in the desired state.
 
.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
        }
    }
}