SqlDatabasePermission

Parameters

Parameter	Attribute	DataType	Description	Allowed Values
DatabaseName	Key	String	The name of the database.
Name	Key	String	The name of the user that should be granted or denied the permission.
PermissionState	Key	String	The state of the permission. Valid values are 'Grant' or 'Deny'.	Grant, Deny, GrantWithGrant
InstanceName	Key	String	The name of the SQL instance to be configured.
Permissions	Required	StringArray[]	The set of permissions for the SQL database.
Ensure	Write	String	If the values should be present or absent. Valid values are 'Present' or 'Absent'.	Present, Absent
ServerName	Write	String	The host name of the SQL Server to be configured. Default value is $env:COMPUTERNAME.

Description

The SqlDatabasePermission DSC resource is used to grant, deny or revoke permissions for a user in a database. For more information about permissions, please read the article Permissions (Database Engine).

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 Database Engine 2012 or later.

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 account CONTOSO\SQLAdmin has "Connect" and "Update" SQL Permissions for database "AdventureWorks".

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

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlDatabasePermission 'Grant_SqlDatabasePermissions_SQLAdmin_Db01'
        {
            Ensure               = 'Present'
            Name                 = 'CONTOSO\SQLAdmin'
            DatabaseName         = 'AdventureWorks'
            PermissionState      = 'Grant'
            Permissions          = @('Connect', 'Update')
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlDatabasePermission 'Grant_SqlDatabasePermissions_SQLUser_Db01'
        {
            Ensure               = 'Present'
            Name                 = 'CONTOSO\SQLUser'
            DatabaseName         = 'AdventureWorks'
            PermissionState      = 'Grant'
            Permissions          = @('Connect', 'Update')
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlDatabasePermission 'Grant_SqlDatabasePermissions_SQLAdmin_Db02'
        {
            Ensure               = 'Present'
            Name                 = 'CONTOSO\SQLAdmin'
            DatabaseName         = 'AdventureWorksLT'
            PermissionState      = 'Grant'
            Permissions          = @('Connect', 'Update')
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

Example 2

This example shows how to ensure that the user account CONTOSO\SQLAdmin hasn't "Select" and "Create Table" SQL Permissions for database "AdventureWorks".

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

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlDatabasePermission 'RevokeGrant_SqlDatabasePermissions_SQLAdmin'
        {
            Ensure               = 'Absent'
            Name                 = 'CONTOSO\SQLAdmin'
            DatabaseName         = 'AdventureWorks'
            PermissionState      = 'Grant'
            Permissions          = @('Connect', 'Update')
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlDatabasePermission 'RevokeDeny_SqlDatabasePermissions_SQLAdmin'
        {
            Ensure               = 'Absent'
            Name                 = 'CONTOSO\SQLAdmin'
            DatabaseName         = 'AdventureWorks'
            PermissionState      = 'Deny'
            Permissions          = @('Select', 'CreateTable')
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

Example 3

This example shows how to ensure that the user account CONTOSO\SQLAdmin has "Connect" and "Update" SQL Permissions for database "AdventureWorks".

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

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlDatabasePermission 'Deny_SqlDatabasePermissions_SQLAdmin_Db01'
        {
            Ensure               = 'Present'
            Name                 = 'CONTOSO\SQLAdmin'
            DatabaseName         = 'AdventureWorks'
            PermissionState      = 'Deny'
            Permissions          = @('Select', 'CreateTable')
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlDatabasePermission 'Deny_SqlDatabasePermissions_SQLUser_Db01'
        {
            Ensure               = 'Present'
            Name                 = 'CONTOSO\SQLUser'
            DatabaseName         = 'AdventureWorks'
            PermissionState      = 'Deny'
            Permissions          = @('Select', 'CreateTable')
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlDatabasePermission 'Deny_SqlDatabasePermissions_SQLAdmin_Db02'
        {
            Ensure               = 'Present'
            Name                 = 'CONTOSO\SQLAdmin'
            DatabaseName         = 'AdventureWorksLT'
            PermissionState      = 'Deny'
            Permissions          = @('Select', 'CreateTable')
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}