SqlDatabaseRole: Incorrect Parameter in Add-SqlDscDatabaseRoleMember

[Rob-S]

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

In the Add-SqlDscDatabaseRoleMember function in the MSFT_SqlDatabaseRole Resource, the first line in the existing code below tests for a Role using the Member name rather than the Role Name. This will always evaluate to false (unless there happens to be a role with the same name as the user name).

if (-not ($SqlDatabaseObject.Roles[$Member] -or $SqlDatabaseObject.Users[$Member]))
{
    $errorMessage = $script:localizedData.DatabaseRoleOrUserNotFound -f $Member, $databaseName

Verbose logs showing the problem

N/A

Suggested solution to the issue

The lines above in MSFT_SqlDatabaseRole should be changed to:

if (-not ($SqlDatabaseObject.Roles[$Name] -and $SqlDatabaseObject.Users[$Member]))
{
    $errorMessage = $script:localizedData.DatabaseRoleOrUserNotFound -f $Member, $Name, $databaseName

In other words, both the Role[$Name] must exist and the User must exist. If "-not" then the error message is displayed.

The string in MSFT_SqlDatabaseRole.strings.psd1 would also need to be changed to:

DatabaseRoleOrUserNotFound = The role '{1}' or user '{0}' does not exist in database '{2}'.

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

N/A

SQL Server edition and version the target node is running

N/A

SQL Server PowerShell modules present on the target node

Name Version Path

---

SqlServer 21.1.18218 C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18218\SqlServer.psd1

The operating system the target node is running

OsName : Microsoft Windows 10 Pro
OsOperatingSystemSKU : 48
OsArchitecture : 64-bit
WindowsVersion : 1909
WindowsBuildLabEx : 18362.1.amd64fre.19h1_release.190318-1202
OsLanguage : en-US
OsMuiLanguages : {en-US}

Version and build of PowerShell the target node is running

Name Value

---

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

Version of the DSC module that was used

Name Version Path

---

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

[Rob-S]

Also, a suggestion while changing the strings.... The "DesiredMembersNotPresent" message can also be displayed when all the desired members are present but there are extraneous members in the role. The string can be changed to make it more accurate:
DesiredMembersNotPresent = One or more of the desired members are not present and/or are extraneous in the role '{0}' in database '{1}'.