SqlDatabaseObjectPermission

SqlDatabaseObjectPermission

Parameters

Parameter	Attribute	DataType	Description	Allowed Values
InstanceName	Key	String	Specifies the name of the SQL Server instance to be configured.
DatabaseName	Key	String	Specifies the name of the database where the object resides.
SchemaName	Key	String	Specifies the name of the schema for the database object.
ObjectName	Key	String	Specifies the name of the database object to set permission for. Can be an empty value when setting permission for a schema.
ObjectType	Key	String	Specifies the type of the database object specified in parameter ObjectName.	Schema, Table, View, StoredProcedure
Name	Key	String	Specifies the name of the database user, user-defined database role, or database application role that will have the permission.
Permission	Required	DSC_DatabaseObjectPermission[]	Specifies the permissions for the database object and the principal. The permissions is an array of embedded instances of the DSC_DatabaseObjectPermission CIM class.
ServerName	Write	String	Specifies the host name of the SQL Server to be configured. Default value is the current computer name.
Force	Write	Boolean	Specifies that permissions that has parameter Ensure set to 'Present' (the default value for permissions) should always be enforced even if that encompasses cascading revocations. An example if the desired state is 'Grant' but the current state is 'GrantWithGrant'. If parameter Force is set to $true the With Grant permission is revoked, if set to $false an exception is thrown since the desired state could not be set. Default is to throw an exception.

DSC_DatabaseObjectPermission

Parameters

Parameter	Attribute	DataType	Description	Allowed Values
State	Key	String	Specifies the state of the permission.	Grant, Deny, GrantWithGrant
Permission	Key	String	Specifies a set of permissions. Valid permission names can be found in the article ObjectPermissionSet Class properties.
Ensure	Write	String	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'.	Present, Absent

Description

The SqlDatabaseObjectPermission DSC resource manage the permissions of database objects in a database for a SQL Server instance.

For more information about permission names that can be managed, see the property names of the ObjectPermissionSet class.

Caution

When revoking permission with PermissionState 'GrantWithGrant', both the grantee and all the other users the grantee has granted the same permission to, will also get their permission revoked.

Requirements

• Target machine must be running Windows Server 2012 or later.
• Target machine must be running SQL Server 2012 or later.
• Target machine must have access to the SQLPS PowerShell module or the SqlServer PowerShell module.

Known issues

All issues are not listed here, see here for all open issues.

Examples

Example 1

This example shows how to ensure that the user 'TestAppRole' is given the desired permission for a table in the database "AdventureWorks".

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlDatabaseObjectPermission 'Table1_TestAppRole_Permission'
        {
            ServerName           = 'testclu01a'
            InstanceName         = 'sql2014'
            DatabaseName         = 'AdventureWorks'
            SchemaName           = 'dbo'
            ObjectName           = 'Table1'
            ObjectType           = 'Table'
            Name                 = 'TestAppRole'
            Permission           = @(
                DSC_DatabaseObjectPermission
                {
                    State      = 'GrantWithGrant'
                    Permission = 'Select'
                }

                DSC_DatabaseObjectPermission
                {
                    State      = 'Grant'
                    Permission = 'Update'
                }

                DSC_DatabaseObjectPermission
                {
                    State      = 'Deny'
                    Permission = 'Delete'
                }

                DSC_DatabaseObjectPermission
                {
                    State      = 'Deny'
                    Permission = 'Alter'
                }
            )

            PSDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

Example 2

This example shows how to revoke permissions for the user 'TestAppRole' for a table in the database "AdventureWorks".

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlDatabaseObjectPermission 'Table1_TestAppRole_Permission'
        {
            ServerName           = 'testclu01a'
            InstanceName         = 'sql2014'
            DatabaseName         = 'AdventureWorks'
            SchemaName           = 'dbo'
            ObjectName           = 'Table1'
            ObjectType           = 'Table'
            Name                 = 'TestAppRole'
            Permission           = @(
                DSC_DatabaseObjectPermission
                {
                    State      = 'GrantWithGrant'
                    Permission = 'Select'
                    Ensure     = 'Absent'
                }

                DSC_DatabaseObjectPermission
                {
                    State      = 'Grant'
                    Permission = 'Update'
                    Ensure     = 'Absent'
                }

                DSC_DatabaseObjectPermission
                {
                    State      = 'Deny'
                    Permission = 'Delete'
                    Ensure     = 'Absent'
                }

                DSC_DatabaseObjectPermission
                {
                    State      = 'Deny'
                    Permission = 'Alter'
                    Ensure     = 'Absent'
                }
            )

            PSDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}