SqlDatabaseObjectPermission

SqlDatabaseObjectPermission

Parameters

Parameter	Attribute	DataType	Description	Allowed Values
InstanceName	Key	String	Specifies the name of the SQL 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	InstanceArray[]	Specifies the permissions as 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 $env:COMPUTERNAME.
State	Key	String	Specifies the state of the permission. Valid values are 'Grant', 'Deny' and 'GrantWithGrant'.	Grant, Deny, GrantWithGrant
Permission	Required	StringArray[]	Specifies the set of permissions for the database object for the principal assigned to 'Name'.
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.

Note: 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.

Embedded instance DSC_DatabaseObjectPermission

• [String] State (Key): Specifies the state of the permission. Valid values are 'Grant', 'Deny' and 'GrantWithGrant'.
• [String[]] Permission (Required): Specifies the set of permissions for the database object for the principal assigned to 'Name'. Valid permission names can be found in the article ObjectPermissionSet Class properties.
• [String] Ensure (Key): 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'.

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', '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', 'Alter')
                    Ensure     = 'Absent'
                }
            )

            PSDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}