SqlDatabasePermission: Possible to both grant and deny same permission for a user

[johlju]

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

With the current implementation of the resource SqlDatabasePermission it is possible to both grant and deny the same permission in the same configuration. This is due to the PermissionState property have the type qualifier Key.

Verbose logs showing the problem

None.

Suggested solution to the issue

Change the property PermissionState and Permission properties to an embedded instance array. That embedded instance class can have those properties in it. Leaving the key properties to be only name, database name, and instance name. Making sure the cannot ber any duplicated instances that can set competing permissions (prevents ping-pong behavior).

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

        SqlDatabasePermission 'Grant_User1'
        {
            Ensure               = 'Present'
            Name                 = 'CONTOSO\User1'
            DatabaseName         = 'AdventureWorks'
            PermissionState      = 'Grant'
            Permissions          = 'Connect', 'Update'
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
        }

        SqlDatabasePermission 'Deny_User1'
        {
            Ensure               = 'Present'
            Name                 = 'CONTOSO\User1'
            DatabaseName         = 'AdventureWorks'
            PermissionState      = 'Deny'
            Permissions          = 'Connect', 'Update'
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
        }

SQL Server edition and version the target node is running

All

SQL Server PowerShell modules present on the target node

Any

The operating system the target node is running

Any

Version and build of PowerShell the target node is running

Any

Version of the DSC module that was used

master

[johlju]

Suggest we refactor this into a class-based resource based on the pattern used for the classes in DnsServerDsc.