SqlDatabaseRole: Conflict when assigning both present and absent roles for same user

[A143717]

Details of the scenario you tried and the problem that is occurring

I want to configure a database and ensure a group of users only have read access. I want to ensure a user is present in the db_datareader role AND is absent from the db_datawriter role.

There is a key property constraint preventing both Present and Absent roles.

How do I configure a database to ensure users only have the db_datareader role?

Verbose logs showing the problem

Test-ConflictingResources : A conflict was detected between resources '[SqlDatabaseRole]TEMP_DB.DataReader.Contoso\mike2' and '[SqlDatabaseRole]TEMP_DB.DataWriter.Contoso\mike2' in node 'localhost'. Resources have identical key properties but there are differences in the following 
non-key properties: 'Role;Ensure'. Values 'db_datareader;Present' don't match values 'db_datawriter;Absent'. Please update these property values so that they are 
identical in both cases.

Suggested solution to the issue

Add Ensure as a key property so you can specify both Present and Absent roles?
Modify SqlDatabaseRole so that when you ensure a user is present on specified roles it automatically ensures the user is absent on all other roles? (yuk hidden dependencies)

The DSC configuration that is used to reproduce the issue (as detailed as possible)

Configuration MultipleDatabaseUsers
{
    Import-DscResource -ModuleName SqlServerDsc -ModuleVersion 12.4.0.0

    # Read-only databases 
    $Databases = ("TEMP_DB")
    
    $Accounts = (     # Hashtable of IDs 
        @{ID = "Contoso\mike2"; Name = "Mike Jones"},
        @{ID = "Contoso\john4"; Name = "John Smith"},
    )

    Node localhost
    {

        # Ensure user has server level Login so they can connect to the server
        ForEach($user in $Accounts) {

            SqlServerLogin "$($user.ID).$($user.Name)"
            {
                Ensure               = "Present"
                Name                 = "$($user.ID)"
                LoginType            = "WindowsUser"
                ServerName           = "localhost"
                InstanceName         = "MSSQLSERVER"
                Disabled             = $false
            }     
        }       
        
        ForEach($Database in $Databases) {
            SqlDatabase "$Database" 
            {
                Ensure       = "Present"
                Name         = "$Database"
                ServerName   = "localhost"  
                InstanceName = "MSSQLSERVER"
                Collation    = "SQL_Latin1_General_CP850_BIN2"
            }
            
            ForEach($user in $Accounts) {
                SqlDatabaseRole "$Database.DataReader.$($user.ID)"
                {
                    DependsOn            = "[SqlServerLogin]$($user.ID).$($user.Name)","[SqlDatabase]$($Database)"
                    Ensure               = "Present"
                    Name                 = "$($user.ID)"
                    Role                 = "db_datareader"    
                    Database             = "$Database"
                    ServerName           = "localhost"
                    InstanceName         = "MSSQLSERVER"
                }

                SqlDatabaseRole "$Database.DataWriter.$($user.ID)"
                {
                    DependsOn            = "[SqlServerLogin]$($user.ID).$($user.Name)","[SqlDatabase]$($Database)"
                    Ensure               = "Absent"
                    Name                 = "$($user.ID)"
                    Role                 = "db_datawriter"    
                    Database             = "$Database"
                    ServerName           = "localhost"
                    InstanceName         = "MSSQLSERVER"
                }

            }
                 
        }
    }
}

SQL Server edition and version the target node is running

Microsoft SQL Server 2017 (RTM-CU5) (KB4092643) - 14.0.3023.8 (X64) Mar 2 2018 18:24:44 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

SQL Server PowerShell modules present on the target node

SqlServer 21.1.18102 C:\Users\_\Documents\WindowsPowerShell\Modules\SqlServer\21.1.18102\SqlServer.psd1
SqlServer 21.1.18102 C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18102\SqlServer.psd1            
SQLPS     14.0       C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1 

The operating system the target node is running

OsName               : Microsoft Windows Server 2016 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture       : 64-bit
WindowsBuildLabEx    : 14393.2248.amd64fre.rs1_release.180427-1804
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

Version and build of PowerShell the target node is running

5.1.14393.2248

Version of the DSC module that was used ('dev' if using current dev branch)

SqlServerDsc 12.4.0.0 C:\Users\_\Documents\WindowsPowerShell\Modules\SqlServerDsc\12.4.0.0\SqlServerDsc.psd1
SqlServerDsc 11.2.0.0 C:\Users\_\Documents\WindowsPowerShell\Modules\SqlServerDsc\11.2.0.0\SqlServerDsc.psd1
SqlServerDsc 12.4.0.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\12.4.0.0\SqlServerDsc.psd1

[johlju]

Maybe we need to change this to Role, RolesToInclude, and RolesToExclude. The last two is mutually exclusive with the first parameter.

• Role means only these roles, remove all other roles.
• RolesToInclude means add user to these, and leave existing. Same role should not be allowed in RolesToExclude in the same configuration.
• RolesToExclude means remove these, and keep the rest. Same role should not be allowed in RolesToInclude in the same configuration.

Similar ro https://github.com/PowerShell/SqlServerDsc#sqlserverrole.