SqlDatabaseObjectPermission: New resource proposal

[XabiBeltza]

I would like to request a resource to manage object level permissions.
For example,

SqlObjectPermission Grant_SqlDatabasePermissions_SQLAdmin_Db01
        {
            Ensure               = 'Present'
            Name                 = 'CONTOSO\SQLAdmin'
            Database             = 'AdventureWorks'
            Object               = 'Table_or_view'
            PermissionState      = 'Grant'
            Permissions          = 'Select'
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            PsDscRunAsCredential = $SqlAdministratorCredential
        }

[johlju]

Looking at this it seems it pretty close to how SqlDatabasePermission works. Would the below schema work?

[ClassVersion("1.0.0.0"), FriendlyName("SqlDatabaseObjectPermission")]
class MSFT_SqlDatabaseObjectPermission : OMI_BaseResource
{
    [Key, Description("The name of the SQL instance to be configured.")] String InstanceName;
    [Key, Description("The name of the database where the object resides.")] String DatabaseName;
    [Key, Description("The name of the database object schema that the object name belongs to.")] String SchemaName;
    [Key, Description("The name of the database object to set permission for.")] String ObjectName;
    [Key, Description("The name of the database user och database role that will have the permission.")] String UserName;
    [Key, Description("The state of the permission. Valid values are 'Grant', 'Deny' and 'GrantWithGrant'."), ValueMap{"Grant","Deny","GrantWithGrant"}, Values{"Grant","Deny","GrantWithGrant"}] String PermissionState;
    [Required, Description("The set of permissions for the database object for the principal assigned to 'Name'.")] String Permissions[];
    [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;
    [Write, Description("The hostname of the SQL Server to be configured. Defaults to $env:COMPUTERNAME.")] String ServerName;
};

I think the properties PermissionState and Permission need to be an array of embedded instances so that it is not possible to grant and deny in the same configuration.

[johlju]

I have labeld this as 'help wanted' so that someone in the community can run with it.

[mreinli]

I am looking for the exact same resource and already started working on it.

[johlju]

@mreinli Awesome! Do you want to send it in as a PR once you are done? Happy to have that resource. 😃

[mreinli]

Sure, will send a PR once it's done. Looking good so far. We most probably need an additional parameter "ObjectType".

[johlju]

@mreinli Awesome! Sounds good with ObjectType. Will ObjectType be key as well? So two types of object can have the same name?

Labeling this as in progress.

[mreinli]

Two objects can have the same name and be in different schemas. Plus, there are database collations which are case-sensitive. I have a working version but am still testing and fine-tuning. Should I already create a PR and update it with changes or wait until it's ready?

[johlju]

Send in the PR when you feel it is ready. 🙂

[XabiBeltza]

Awesome to see so much traction on this!