SqlDatabaseRole: Conflict when assigning two roles to the same user

[ChristophHannappel]

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

Trying to assign a user two database roles on the same database.

Verbose logs showing the problem

Test-ConflictingResources : A conflict was detected between resources '[SqlDatabaseRole]SP-ConfigDB-db_owner-FarmAccount
(E:\DSC\Configuration\SP2016-Configuration.ps1::289::13::SqlDatabaseRole)' and
'[SqlDatabaseRole]SP-ConfigDB-WSS_Content_Application_Pools-FarmAccount
(E:\DSC\Configuration\SP2016-Configuration.ps1::301::13::SqlDatabaseRole)' in node 'NodeName'. Resources have
identical key properties but there are differences in the following non-key properties: 'Role'. Values 'db_owner' don't match
values 'WSS_Content_Application_Pools'. Please update these property values so that they are identical in both cases.
At line:289 char:9

•     Test-ConflictingResources $keywordName $canonicalizedValue $k ...
  

•     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : InvalidOperation: (:) [Write-Error], InvalidOperationException
  • FullyQualifiedErrorId : ConflictingDuplicateResource,Test-ConflictingResources

Suggested solution to the issue

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

SqlDatabaseRole SP-ConfigDB-db_owner-FarmAccount
            {
                Name = $SPFarmAccount.Username.toString()
                ServerName = $SPServiceDatabaseServerAlias
                InstanceName = 'MSSQLSERVER'
                Database = ($ConfigurationData.NonNodeData.SPServiceDatabasePrefix + 'Config')
                Role = 'db_owner'
                Ensure = 'Present'
                PsDscRunAsCredential = $SPSetupAccount
                DependsOn = '[SqlDatabaseOwner]SP-ConfigDB-DatabaseOwner'
            }
            # Der Farm Account erhält die 'WSS_Content_Application_Pools' Rolle auf der der SharePoint Config Datenbank
            SqlDatabaseRole SP-ConfigDB-WSS_Content_Application_Pools-FarmAccount
            {
                Name = $SPFarmAccount.Username.toString()
                ServerName = $SPServiceDatabaseServerAlias
                InstanceName = 'MSSQLSERVER'
                Database = ($ConfigurationData.NonNodeData.SPServiceDatabasePrefix + 'Config')
                Role = 'WSS_Content_Application_Pools'
                Ensure = 'Present'
                PsDscRunAsCredential = $SPSetupAccount
                DependsOn = '[SqlDatabaseOwner]SP-ConfigDB-DatabaseOwner'
            }

SQL Server edition and version the target node is running

Microsoft SQL Server 2017 (RTM-CU11) (KB4462262) - 14.0.3038.14 (X64) Sep 14 2018 13:53:44 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

SQL Server PowerShell modules present on the target node

Name Version Path

---

sqlserver 21.0.17279 C:\Program Files\WindowsPowerShell\Modules\sqlserver\sqlserver.psd1

The operating system the target node is running

OsName : Microsoft Windows Server 2016 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture : 64-bit
WindowsBuildLabEx : 14393.2485.amd64fre.rs1_release.180827-1809
OsLanguage : en-US
OsMuiLanguages : {en-US}

Version and build of PowerShell the target node is running

Name Value

---

PSVersion 5.1.14393.2485
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14393.2485
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

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

SqlServerDsc 12.0.0.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\SqlServerDsc.psd1

[johlju]

Role is an array of roles. Currently the resource is created in such a way that the resource should be specified one time for each user, and all the roles that should be added should go as an array in the Role parameter/property.

[johlju]

I think is discussed in issue #1002 too?

[stale]

This issue has been automatically marked as stale because it has not had activity from the community in the last 30 days. It will be closed if no further activity occurs within 10 days. If the issue is labelled with any of the work labels (e.g bug, enhancement, documentation, or tests) then the issue will not auto-close.

[stale]

This issue has been automatically closed because it is has not had activity from the community in the last 40 days.