SQLServerRole uses contains() which is case sensitive

[brwilkinson]

get-module -name sqlserverdsc -ListAvailable

Directory: C:\Program Files\WindowsPowerShell\Modules

ModuleType Version Name
Manifest 11.2.0.0 SqlServerDsc

https://github.com/PowerShell/SqlServerDsc/blob/dev/DSCResources/MSFT_SqlServerRole/MSFT_SqlServerRole.psm1#L113

Get-DscResource sqlserverrole -syntax

SqlServerRole [String] #ResourceName
{
    InstanceName = [string]
    ServerName = [string]
    ServerRoleName = [string]
    [DependsOn = [string[]]]
    [Ensure = [string]{ Absent | Present }]
    [Members = [string[]]]
    [MembersToExclude = [string[]]]
    **[MembersToInclude = [string[]]]**
    [PsDscRunAsCredential = [PSCredential]]
}

The MemberstoInclude property currently uses the contains method.

if ( -not ($membersInRole.Contains($memberToInclude)))

This is case sensitive . . .

e.g

$lista = 'abc','123'
$lista.Contains('abc')
> True
$lista.Contains('Abc')
> False

This is not desirable since possible the domain name or usename may be in a different case that the account already present on SQL.

The workaround might be to use the -In or the -contains operator.

$lista -contains 'abc'
>True
$lista -contains 'Abc'
> True

[johlju]

Thanks for reporting this! Labeling this as bug and help wanted so anyone in the community can run with this. Would be great to get a regression test for this too, so the same problem does not return in the future. 🙂

[paoIpao]

Same problem.

[johlju]

It is supported in SQL Server to have the same login with different casing (in sensitive collations). But it will fail when compiling the mof-file as mentioned here in issue #1191.

[johlju]

This was resolved in PR #1453 since we can't enforce case-sensitivity in a mof resource.