en-US/about_SqlDatabasePermission.help.txt

.NAME
    SqlDatabasePermission
 
.SYNOPSIS
    The SqlDatabasePermission DSC resource is used to grant, deny or revoke
    permissions for a user in a database.
 
.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 https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine.
 
    > [!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 Database Engine 2012 or later.
 
    ## Known issues
 
    All issues are not listed here, see https://github.com/dsccommunity/SqlServerDsc/issues?q=is%3Aissue+is%3Aopen+in%3Atitle+SqlDatabasePermission.
 
    ### PSDscRunAsCredential not supported
 
    The built-in property PSDscRunAsCredential does not work with class-based
    resources that using advanced type like the parameters Permission and
    Reasons has. Use the parameter Credential instead of PSDscRunAsCredential.
 
    ### Using Credential property.
 
    SQL Authentication and Group Managed Service Accounts is not supported as
    impersonation credentials. Currently only Windows Integrated Security is
    supported to use as credentials.
 
    For Windows Authentication the username must either be provided with the User
    Principal Name (UPN), e.g. 'username@domain.local' or if using non-domain
    (for example a local Windows Server account) account the username must be
    provided without the NetBIOS name, e.g. 'username'. The format 'DOMAIN\username'
    will not work.
 
    See more information in https://github.com/dsccommunity/SqlServerDsc/wiki/CredentialOverview.
 
    ### Invalid values during compilation
 
    The parameter Permission is of type [DatabasePermission]. If a property
    in the type is set to an invalid value an error will occur, correct the
    values in the properties to valid values.
    This happens when the values are validated against the [ValidateSet()]
    of the resource. When there is an invalid value the following error will
    be thrown when the configuration is run (it will not show during compilation):
 
    `plaintext
    Failed to create an object of PowerShell class SqlDatabasePermission.
        + CategoryInfo : InvalidOperation: (root/Microsoft/...ConfigurationManager:String) [], CimException
        + FullyQualifiedErrorId : InstantiatePSClassObjectFailed
        + PSComputerName : localhost
    `
 
.PARAMETER DatabaseName
    Key - System.String
    The name of the database.
 
.PARAMETER Name
    Key - System.String
    The name of the user that should be granted or denied the permission.
 
.PARAMETER Permission
    Write - DatabasePermission[]
    An array of database permissions to enforce. Any permission that is not
    part of the desired state will be revoked.
 
    Must provide all permission states (Grant, Deny, GrantWithGrant) with
    at least an empty string array for the advanced type DatabasePermission's
    property Permission.
 
    Valid permission names can be found in the article https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.databasepermissionset#properties.
 
    This is an array of CIM instances of advanced type DatabasePermission from
    the namespace root/Microsoft/Windows/DesiredStateConfiguration.
 
.PARAMETER PermissionToInclude
    Write - DatabasePermission[]
    An array of database permissions to include to the current state. The
    current state will not be affected unless the current state contradict the
    desired state. For example if the desired state specifies a deny permissions
    but in the current state that permission is granted, that permission will
    be changed to be denied.
 
    Valid permission names can be found in the article https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.databasepermissionset#properties.
 
    This is an array of CIM instances of advanced type DatabasePermission from
    the namespace root/Microsoft/Windows/DesiredStateConfiguration.
 
.PARAMETER PermissionToExclude
    Write - DatabasePermission[]
    An array of database permissions to exclude (revoke) from the current state.
 
    Valid permission names can be found in the article https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.databasepermissionset#properties.
 
    This is an array of CIM instances of advanced type DatabasePermission from
    the namespace root/Microsoft/Windows/DesiredStateConfiguration.
 
.EXAMPLE 1
 
This example shows how to ensure that the user account CONTOSO\SQLAdmin
is granted "Connect" and "Update" permissions for the databases DB01 and
DB02. It also shows that the user account CONTOSO\SQLUser has is granted
"Connect" and "Update" permissions, but also how it is denied the permission
"Delete" for the database DB01.
 
Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )
 
    Import-DscResource -ModuleName 'SqlServerDsc'
 
    node localhost
    {
        SqlDatabasePermission 'Set_Database_Permissions_SQLAdmin_DB01'
        {
            ServerName = 'sql01.company.local'
            InstanceName = 'DSC'
            DatabaseName = 'DB01'
            Name = 'CONTOSO\SQLAdmin'
            Credential = $SqlAdministratorCredential
            Permission = @(
                DatabasePermission
                {
                    State = 'Grant'
                    Permission = @('Connect', 'Update')
                }
                DatabasePermission
                {
                    State = 'GrantWithGrant'
                    Permission = @()
                }
                DatabasePermission
                {
                    State = 'Deny'
                    Permission = @()
                }
            )
        }
 
        SqlDatabasePermission 'Set_Database_Permissions_SQLAdmin_DB02'
        {
            ServerName = 'sql01.company.local'
            InstanceName = 'DSC'
            DatabaseName = 'DB02'
            Name = 'CONTOSO\SQLAdmin'
            Credential = $SqlAdministratorCredential
            Permission = @(
                DatabasePermission
                {
                    State = 'Grant'
                    Permission = @('Connect', 'Update')
                }
                DatabasePermission
                {
                    State = 'GrantWithGrant'
                    Permission = @()
                }
                DatabasePermission
                {
                    State = 'Deny'
                    Permission = @()
                }
            )
        }
 
        SqlDatabasePermission 'Set_Database_Permissions_SQLUser_DB01'
        {
            ServerName = 'sql01.company.local'
            InstanceName = 'DSC'
            DatabaseName = 'DB01'
            Name = 'CONTOSO\SQLUser'
            Credential = $SqlAdministratorCredential
            Permission = @(
                DatabasePermission
                {
                    State = 'Grant'
                    Permission = @('Connect', 'Update')
                }
                DatabasePermission
                {
                    State = 'GrantWithGrant'
                    Permission = @()
                }
                DatabasePermission
                {
                    State = 'Deny'
                    Permission = @('Delete')
                }
            )
        }
    }
}
 
.EXAMPLE 2
 
This example shows how to enforce that if the user account CONTOSO\SQLAdmin
in the databases DB01 would be granted the permission "Delete" outside of
DSC, it is revoked.
Any other existing permissions in the states Grant, Deny, and GrantWithGrant
will not be changed (unless the contradict with the desired state).
 
Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )
 
    Import-DscResource -ModuleName 'SqlServerDsc'
 
    node localhost
    {
        SqlDatabasePermission 'Set_Database_Permissions_SQLAdmin_DB01'
        {
            ServerName = 'sql01.company.local'
            InstanceName = 'DSC'
            DatabaseName = 'DB01'
            Name = 'CONTOSO\SQLAdmin'
            Credential = $SqlAdministratorCredential
            PermissionToExclude = @(
                DatabasePermission
                {
                    State = 'Grant'
                    Permission = @('Delete')
                }
            )
        }
    }
}
 
.EXAMPLE 3
 
This example shows how to ensure that the user account CONTOSO\SQLAdmin
is granted "Connect" and "Update" permissions for the databases DB01.
Any existing permissions in the states Grant, Deny, and GrantWithGrant will
not be changed (unless the contradict with the desired state).
 
Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )
 
    Import-DscResource -ModuleName 'SqlServerDsc'
 
    node localhost
    {
        SqlDatabasePermission 'Set_Database_Permissions_SQLAdmin_DB01'
        {
            ServerName = 'sql01.company.local'
            InstanceName = 'DSC'
            DatabaseName = 'DB01'
            Name = 'CONTOSO\SQLAdmin'
            Credential = $SqlAdministratorCredential
            PermissionToInclude = @(
                DatabasePermission
                {
                    State = 'Grant'
                    Permission = @('Connect', 'Update')
                }
            )
        }
    }
}