SqlDatabasePermission

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.	Grant, Deny, GrantWithGrant
InstanceName	Key	String	The name of the SQL Server instance to be configured. Default value is 'MSSQLSERVER'.
Permissions	Required	StringArray[]	The permissions to be granted or denied for the user in the database.
Ensure	Write	String	If the permission should be granted ('Present') or revoked ('Absent').	Present, Absent
ServerName	Write	String	The host name of the SQL Server to be configured. Default value is the current computer name.

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.

Valid permission names can be found in the article DatabasePermissionSet Class properties.

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
        }
    }
}