SqlDatabaseObjectPermission: Fails to compile when granting select and execute on different objects.

[DataBeardAdmin]

Problem description

When compiling a DSC configuration with two SqlDatabaseObjectPermission resources, I receive the error message

Test-ConflictingResources : A conflict was detected between resources ' ()' and ' ()' in node 'TestComputer'. Resources have identical key properties but there are differences in the following non-key properties: 'Permission'. Values 'Select' don't match values 'Execute'.
Please update these property values so that they are identical in both cases.

I'm using grants of different permission types on different objects. For example, if I try to grant execute to a procedure and select to a table in the same configuration, I receive the error above. If I only grant a single permission type, the error is not generated. This only occurs when different permissions are granted in the same configuration.

Verbose logs

Test-ConflictingResources : A conflict was detected between resources ' ()' and ' ()' in node 'TestComputer'. Resources have identical key properties but there are differences in the following non-key properties: 'Permission'. Values 'Select' don't match values 'Execute'. 
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
Compilation errors occurred while processing configuration 'DscResourceConflict'. Please review the errors reported in error stream and modify your configuration code appropriately.
At C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules\PSDesiredStateConfiguration\PSDesiredStateConfiguration.psm1:3917 char:5
+     throw $ErrorRecord
+     ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (DscResourceConflict:String) [], InvalidOperationException
    + FullyQualifiedErrorId : FailToProcessConfiguration

DSC configuration

Configuration DscResourceConflict {
    param ()
    Import-DscResource -ModuleName SqlServerDsc

    Node 'TestComputer' {
        SqlDatabaseObjectPermission 'Test1'
        {
            ServerName = 'localhost'
            InstanceName = 'MSSQLSERVER'
            DatabaseName = 'Test'
            SchemaName = 'dbo'
            ObjectName = 'MyTable'
            ObjectType = 'Table'
            Name = 'CustomRole'
            Permission = @(
                DSC_DatabaseObjectPermission
                {
                    State      = 'Grant'
                    Permission = 'Select'
                }
            )
        }

        SqlDatabaseObjectPermission 'Test2'
        {
            ServerName = 'localhost'
            InstanceName = 'MSSQLSERVER'
            DatabaseName = 'Test'
            SchemaName = 'dbo'
            ObjectName = 'MyProcedure'
            ObjectType = 'StoredProcedure'
            Name = 'CustomRole'
            Permission = @(
                DSC_DatabaseObjectPermission
                {
                    State      = 'Grant'
                    Permission = 'Execute'
                }
            )
        }
    }
}

DscResourceConflict

Suggested solution

Change the definition of the class DSC_DatabaseObjectPermission in the file DSC_SqlDatabaseObjectPermission.schema.mof to make the Permission property a key and remove the array from this property.

class DSC_DatabaseObjectPermission
{
    [Key, Description("Specifies the state of the permission."), ValueMap{"Grant","Deny","GrantWithGrant"}, Values{"Grant","Deny","GrantWithGrant"}] String State;
    [Key, Description("Specifies a set of permissions. Valid permission names can be found in the article [ObjectPermissionSet Class properties](https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.objectpermissionset#properties).")] String Permission;
    [Write, Description("Specifies the desired state of the permission. When set to `'Present'` the permissions will be added. When set to `'Absent'` the permissions will be removed. Default value is `'Present'`."), ValueMap{"Present","Absent"}, Values{"Present","Absent"}] String Ensure;
};

Change the parameter $Permission of ConvertTo-CimDatabaseObjectPermission to remove the array in DSC_SqlDatabaseObjectPermission.psm1 to align with the updated class definition.

Note: This would be a breaking change in that each permission will need to be defined individually.

SQL Server edition and version

This is not specific to any target version.  The error occurs during compile.

SQL Server PowerShell modules

This does not appear to be version specific as I have been able to duplicate the error on multiple machines with different versions of the SqlServer module installed.  The latest test utilized SqlServer 21.1.18256

Operating system

OsName               : Microsoft Windows 10 Pro
OsOperatingSystemSKU : 48
OsArchitecture       : 64-bit
WindowsVersion       : 2009
WindowsBuildLabEx    : 19041.1.amd64fre.vb_release.191206-1406
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

OsName               : Microsoft Windows Server 2012 R2 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture       : 64-bit
WindowsBuildLabEx    : 9600.20111.amd64fre.winblue_ltsb_escrow.210812-0920
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

PowerShell version

Name                           Value
----                           -----
PSVersion                      5.1.19041.1237
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.19041.1237
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

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

SqlServerDsc version

SqlServerDsc 15.2.0

[johlju]

It never occurred to me that the nested instances would be in conflict. I wonder if this can be solved using a class-based resource instead of a MOF-resources, so we can keep the array to avoid a breaking change. 🤔

[johlju]

From input in ther #dsc channel, most likely a class-based resource will not help. So I'm good with the proposed change even if it is a breaking one. 🙂