SqlDatabaseObjectPermission: INSERT permission never shows as InDesiredState when other permissions are on the table

[markaugust]

Problem description

When you are only granting INSERT permissions on a table, if INSERT is the only permission on the table, everything works as expected. However, when there are permissions on the table other than INSERT (such as SELECT/GRANT/UPDATE), the testing of the resource will always show InDesiredState = False

Here is a screenshot showing with just INSERT it works as expected:

Here is a screenshot showing that even though both the DELETE and INSERT permissions have been set, it's stating that the permissions are not in a desired state:

---

I believe it has something to do with this line in the code. if ($true -in $currentObjectPermissions.PermissionType.$currentPermissionProperty)

If I test that code outside of this module, we see that the if ($true -in $currentObjectPermissions.PermissionType.$currentPermissionProperty) block doesn't actually work with INSERT

Pulling the if block test out by itself, we get:

And looking at just CurrentObjectPermissions.PermissionType.Insert we get the following:

Verbose logs

PS C:\tmp> $grantInsert = New-CimInstance -ClientOnly -ClassName 'DSC_DatabaseObjectPermission' -Property @{'state' = 'Grant'; 'permission' = 'INSERT'}
$grantDelete = New-CimInstance -ClientOnly -ClassName 'DSC_DatabaseObjectPermission' -Property @{'state' = 'Grant'; 'permission' = 'DELETE'}
$InvokeParams = @{
    Name = 'SqlDatabaseObjectPermission';
    Method = 'Test';
    Property = @{
        instancename = 'MSSQLSERVER';
        objecttype = 'Table';
        databasename = 'TestDB';
        name = 'CONTOSO\marktest';
        objectname = 'TestTable';
        schemaname = 'dbo';
        permission = [CimInstance[]]@($grantInsert, $grantDelete)
       };
    ModuleName = @{
        ModuleName = 'SQLServerDsc';
        RequiredVersion = '16.5.0'
    }
}
Invoke-DscResource @InvokeParams -Verbose -Debug
VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = ResourceTest,'className' = MSFT_DSCLocalConfigurationManag
er,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.
VERBOSE: An LCM method call arrived from computer SQL01 with user sid S-1-5-21-2749032974-3131961438-382479902-500.
VERBOSE: [SQL01]: LCM:  [ Start  Test     ]  [[SqlDatabaseObjectPermission]DirectResourceAccess]
VERBOSE: [SQL01]:                            [[SqlDatabaseObjectPermission]DirectResourceAccess] Determining the current state of the permissions f
or the database object 'dbo.TestTable' of type 'Table' in the database 'TestDB' for the instance 'MSSQLSERVER' on the server 'SQL01'. (SDOP0002)
VERBOSE: [SQL01]:                            [[SqlDatabaseObjectPermission]DirectResourceAccess] Getting the current state of the permissions for t
he database object 'dbo.TestTable' of type 'Table' in the database 'TestDB' for the instance 'MSSQLSERVER' on the server 'SQL01'. (SDOP0001)
DEBUG: [SQL01]:                            [[SqlDatabaseObjectPermission]DirectResourceAccess] "Sort-Object" - "CalculatedVersion" cannot be found 
in "InputObject".
VERBOSE: [SQL01]:                            [[SqlDatabaseObjectPermission]DirectResourceAccess] Connecting as current user 'NT AUTHORITY\SYSTEM' u
sing integrated security. (SQLCOMMON0054)
VERBOSE: [SQL01]:                            [[SqlDatabaseObjectPermission]DirectResourceAccess] Connected to SQL instance 'SQL01'. (SQLCOMMON0018)
DEBUG: [SQL01]:                            [[SqlDatabaseObjectPermission]DirectResourceAccess] Evaluating the state of the property 'Permission'. (
DRC0038)
DEBUG: [SQL01]:                            [[SqlDatabaseObjectPermission]DirectResourceAccess] Testing CIM instance 'DSC_DatabaseObjectPermission' 
with the key properties 'Permission="INSERT";State="Grant"'. (DRC0030)
DEBUG: [SQL01]:                            [[SqlDatabaseObjectPermission]DirectResourceAccess] String value does not match. Current value is 'Absen
t', but expected the value 'Present'. (DRC0036)
DEBUG: [SQL01]:                            [[SqlDatabaseObjectPermission]DirectResourceAccess] Testing CIM instance 'DSC_DatabaseObjectPermission' 
with the key properties 'Permission="DELETE";State="Grant"'. (DRC0030)
VERBOSE: [SQL01]:                            [[SqlDatabaseObjectPermission]DirectResourceAccess] The permissions for the database object 'dbo.TestT
able' is not in desired state. (SDOP0003)
VERBOSE: [SQL01]: LCM:  [ End    Test     ]  [[SqlDatabaseObjectPermission]DirectResourceAccess] False in 0.1280 seconds.
VERBOSE: [SQL01]: LCM:  [ End    Set      ]    in  0.2110 seconds.
VERBOSE: Operation 'Invoke CimMethod' complete.

InDesiredState 
-------------- 
False          
VERBOSE: Time taken for configuration job to complete is 0.382 seconds

PS C:\tmp>

DSC configuration

$grantInsert = New-CimInstance -ClientOnly -ClassName 'DSC_DatabaseObjectPermission' -Property @{'state' = 'Grant'; 'permission' = 'INSERT'}
$grantDelete = New-CimInstance -ClientOnly -ClassName 'DSC_DatabaseObjectPermission' -Property @{'state' = 'Grant'; 'permission' = 'DELETE'}
$InvokeParams = @{
    Name = 'SqlDatabaseObjectPermission';
    Property = @{
        instancename = 'MSSQLSERVER';
        objecttype = 'Table';
        databasename = 'TestDB';
        name = 'CONTOSO\marktest';
        objectname = 'TestTable';
        schemaname = 'dbo';
        permission = [CimInstance[]]@($grantInsert, $grantDelete)
       };
    ModuleName = @{
        ModuleName = 'SQLServerDsc';
        RequiredVersion = '16.5.0'
    }
}
Invoke-DscResource -Method Set @InvokeParams -Verbose -Debug
Invoke-DscResource -Method Test @InvokeParams -Verbose -Debug

Suggested solution

My proposed solution is this:

If we wrap the if ($true -in $currentObjectPermissions.PermissionType.$currentPermissionProperty) block in it's own foreach loop, then it seems to work.

This is because when you have multiple permissions, the $currentObjectPermissions is an array and has the Insert as an IList method.

But if you look at an individual item in the array, then the Insert is actually a property as expected

SQL Server edition and version

Microsoft SQL Server 2019 (RTM-GDR) (KB5029377) - 15.0.2104.1 (X64)   Aug 16 2023 00:09:21   Copyright (C) 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2019 Standard Evaluation 10.0 <X64> (Build 17763: ) (Hypervisor)

SQL Server PowerShell modules

Name  Version Path                                                                                     
----  ------- ----                                                                                     
SQLPS 15.0    C:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

Operating system

OsName               : Microsoft Windows Server 2019 Standard Evaluation
OsOperatingSystemSKU : 79
OsArchitecture       : 64-bit
WindowsVersion       : 1809
WindowsBuildLabEx    : 17763.1.amd64fre.rs5_release.180914-1434
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

PowerShell version

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

SqlServerDsc version

Name         Version Path                                                                            
----         ------- ----                                                                            
SqlServerDsc 16.5.0  C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\16.5.0\SqlServerDsc.psd1

[johlju]

Thanks for submitting this issue and the detailed run down. Your suggested fix looks good. Do you have time to send in a PR with the fix?

[markaugust]

@johlju I created a pull request. I was able to test in my environment, and that seemed to work, but if you have access to a more robust testing environment, by all means, I'd love some additional eyes on it. I also added an Insert permission object in the unit test. Let me know if that was not appropriate/needed, and I can remove it. The other tasks in the Task List didn't seem appropriate here, but let me know if you disagree and I can make further updates.

[johlju]

Looks good, I made a review and would be great to change the mock according to my comment.

[markaugust]

@johlju Fixed the mock and got the unit tests to pass