SqlDatabaseObjectPermission: setting table permission from "GrantWithGrant" to "Grant" still results in "GrantWithGrant" permissions configured

[nagten]

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

1. Create a DSC configuration to set permission on a table to GrantWithGrant
2. Execute DSC configuration (we do this via the win_dsc Ansible module)
3. Change DSC configuration to set permission on the same table to Grant
4. Execute DSC configuration

Expected result: Succesfull setting of the permission from GrantWithGrant to Grant

Result: Permission on the table is still GrantWithGrant.

Step 1 and 2:

Check rights on table and they are correctly configured as "GrantWithGrant"
PS S:\TESTDSC> .\testSQL.ps1
GrantWithGrant SELECT ON sysjobs TO DOMAIN\SA-TESTACCOUNT

Step 3 and 4:

Check rights on table and instead of Grant the table still has "GrantWithGrant" rights
PS S:\TESTDSC> .\testSQL.ps1
GrantWithGrant SELECT ON sysjobs TO DOMAIN\SA-TESTACCOUNT

Verbose logs showing the problem

changed: [INFRATSTSERVER_42040dd6-bdca-ff2e-eadc-da9d5b9155c5] => {
"changed": true,
"invocation": {
"module_args": {
"DatabaseName": "msdb",
"DependsOn": null,
"InstanceName": "INSTANCE_TST",
"Name": "DOMAIN\SA-TESTACCOUNT",
"ObjectName": "sysjobs",
"ObjectType": "Table",
"Permission": [
{
"Ensure": null,
"Permission": [
"Select"
],
"State": "Grant"
}
],
"PsDscRunAsCredential_password": null,
"PsDscRunAsCredential_username": null,
"SchemaName": "dbo",
"ServerName": "INFRATSTSERVER",
"module_version": "latest",
"resource_name": "SqlDatabaseObjectPermission"
}
},
"module_version": "14.1.0",
"reboot_required": false,
"verbose_set": [
"Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = ResourceSet,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.",
"An LCM method call arrived from computer INFRATSTSERVER with user sid S-1-5-21-1458491821-1856443079-1469108721-500.",
"[INFRATSTSERVER]: LCM: [ Start Set ] [[SqlDatabaseObjectPermission]DirectResourceAccess]",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Getting the current state of the permissions for the database object 'dbo.sysjobs' of type 'Table' in the database 'msdb' for the instance 'INSTANCE_TST_TST' on the server 'INFRATSTSERVER'. (SDOP0001)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Found PowerShell module sqlserver already imported in the session. (SQLCOMMON0026)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Connected to SQL instance 'INFRATSTSERVER\INSTANCE_TST'. (SQLCOMMON0018)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Evaluating the state of the property 'Permission'. (SQLCOMMON0059)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Testing CIM instance 'DSC_DatabaseObjectPermission' with the key properties 'State="Grant"'. (SQLCOMMON0071)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] String value does not match. Current value is 'Absent', but expected the value 'Present'. (SQLCOMMON0063)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] The parameter 'Permission' is not in desired state. (SQLCOMMON0061)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Setting the desired permissions for the database object 'dbo.sysjobs'. (SDOP0009)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Found PowerShell module sqlserver already imported in the session. (SQLCOMMON0026)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Connected to SQL instance 'INFRATSTSERVER\INSTANCE_TST'. (SQLCOMMON0018)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Setting permissions 'Select' for the user 'DOMAIN\SA-TESTACCOUNT' with the state 'Grant' for the database object 'dbo.sysjobs' of type 'Table' in the database 'msdb' (SDOP0007)",
"[INFRATSTSERVER]: LCM: [ End Set ] [[SqlDatabaseObjectPermission]DirectResourceAccess] in 0.1530 seconds.",
"[INFRATSTSERVER]: LCM: [ End Set ] in 0.2030 seconds.",
"Operation 'Invoke CimMethod' complete.",
"Time taken for configuration job to complete is 0.364 seconds"
],
"verbose_test": [
"Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = ResourceTest,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.",
"An LCM method call arrived from computer INFRATSTSERVER with user sid S-1-5-21-1458491821-1856443079-1469108721-500.",
"[INFRATSTSERVER]: LCM: [ Start Test ] [[SqlDatabaseObjectPermission]DirectResourceAccess]",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Determining the current state of the permissions for the database object 'dbo.sysjobs' of type 'Table' in the database 'msdb' for the instance 'INSTANCE_TST_TST' on the server 'INFRATSTSERVER'. (SDOP0002)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Getting the current state of the permissions for the database object 'dbo.sysjobs' of type 'Table' in the database 'msdb' for the instance 'INSTANCE_TST_TST' on the server 'INFRATSTSERVER'. (SDOP0001)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Found PowerShell module sqlserver already imported in the session. (SQLCOMMON0026)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Connected to SQL instance 'INFRATSTSERVER\INSTANCE_TST'. (SQLCOMMON0018)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Evaluating the state of the property 'Permission'. (SQLCOMMON0059)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] Testing CIM instance 'DSC_DatabaseObjectPermission' with the key properties 'State="Grant"'. (SQLCOMMON0071)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] String value does not match. Current value is 'Absent', but expected the value 'Present'. (SQLCOMMON0063)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] The parameter 'Permission' is not in desired state. (SQLCOMMON0061)",
"[INFRATSTSERVER]: [[SqlDatabaseObjectPermission]DirectResourceAccess] The permissions for the database object 'dbo.sysjobs' is not in desired state. (SDOP0003)",
"[INFRATSTSERVER]: LCM: [ End Test ] [[SqlDatabaseObjectPermission]DirectResourceAccess] False in 0.4990 seconds.",
"[INFRATSTSERVER]: LCM: [ End Set ] in 0.5500 seconds.",
"Operation 'Invoke CimMethod' complete.",
"Time taken for configuration job to complete is 0.697 seconds"
]
}

Suggested solution to the issue

See pullrequest

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

Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlDatabaseObjectPermission 'INFRATSTSERVERTablepermissions'
        {
            ServerName           = 'INFRATSTSERVER'
            InstanceName         = 'INSTANCE_TST'
            DatabaseName         = 'msdb'
            SchemaName           = 'dbo'
            ObjectName           = 'sysjobs'
            ObjectType           = 'Table'
            Name                 = 'DOMAIN\\SA-TESTACCOUNT'
            Permission           = @(
                DSC_DatabaseObjectPermission
                {
                    State      = 'GrantWithGrant'
                    Permission = @('Select')
                }
            )

            PSDscRunAsCredential = $SqlAdministratorCredential
        }
    }

Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlDatabaseObjectPermission 'INFRATSTSERVERTablepermissions'
        {
            ServerName           = 'INFRATSTSERVER'
            InstanceName         = 'INSTANCE_TST'
            DatabaseName         = 'msdb'
            SchemaName           = 'dbo'
            ObjectName           = 'sysjobs'
            ObjectType           = 'Table'
            Name                 = 'DOMAIN\\SA-TESTACCOUNT'
            Permission           = @(
                DSC_DatabaseObjectPermission
                {
                    State      = 'Grant'
                    Permission = @('Select')
                }
            )

            PSDscRunAsCredential = $SqlAdministratorCredential
        }
    }

SQL Server edition and version the target node is running

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)

SQL Server PowerShell modules present on the target node

Name Version Path

SQLPS 15.0 C:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1
sqlserver 21.1.18226 C:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\sqlserver\sqlserver.psd1

The operating system the target node is running

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

Version and build of PowerShell the target node is running

Name Value

PSVersion 5.1.17763.1007
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.17763.1007
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 14.1.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\14.1.0\SqlServerDsc.psd1

[johlju]

I have a question how the solution to this should behave.

The solution that is being worked on in PR #1603 is to revoke the "WithGrant" since that is not the desired state. But since the solution needs to cascade the revoke, should this behavior be opt-in for example using a Force parameter? If Force is set to $true then the revoke of WithGrant plus the cascading is done. But if Force is set to $false (the default value) then it throws an exception (or maybe just a warning) saying that the desired state could not be (entirely) set.

I trying to mitigate where a user sets Grant without knowing that GrantWithGrant has been set outside of DSC and then all the permission that user has given is revoked (plus any deny permissions).