SqlServerPermission

Parameters

Parameter	Attribute	DataType	Description	Allowed Values
InstanceName	Key	String	The name of the SQL instance to be configured.
ServerName	Write	String	The host name of the SQL Server to be configured. Default value is $env:COMPUTERNAME.
Ensure	Write	String	If the permission should be present or absent. Default value is 'Present'.	Present, Absent
Principal	Key	String	The login to which permission will be set.
Permission	Write	StringArray[]	The permission to set for the login. Valid values are ConnectSql, AlterAnyAvailabilityGroup, ViewServerState or AlterAnyEndPoint.	ConnectSql, AlterAnyAvailabilityGroup, ViewServerState, AlterAnyEndPoint

Description

The SqlPermission DSC resource sets server permissions to a user (login).

Note: Currently the resource only supports ConnectSql, AlterAnyAvailabilityGroup, AlterAnyEndPoint and ViewServerState.

Requirements

• Target machine must be running Windows Server 2012 or later.
• Target machine must be running SQL Server Database Engine 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 will add the server permissions AlterAnyAvailabilityGroup and ViewServerState to the login 'NT AUTHORITY\SYSTEM' and 'NT SERVICE\ClusSvc' to the default instance.

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

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        # Add permission
        SqlServerPermission 'SQLConfigureServerPermission-SYSTEM'
        {
            Ensure               = 'Present'
            ServerName           = 'SQLNODE01.company.local'
            InstanceName         = 'MSSQLSERVER'
            Principal            = 'NT AUTHORITY\SYSTEM'
            Permission           = 'AlterAnyAvailabilityGroup', 'ViewServerState'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlServerPermission 'SQLConfigureServerPermission-ClusSvc'
        {
            Ensure               = 'Present'
            ServerName           = 'SQLNODE01.company.local'
            InstanceName         = 'MSSQLSERVER'
            Principal            = 'NT SERVICE\ClusSvc'
            Permission           = 'AlterAnyAvailabilityGroup', 'ViewServerState'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

Example 2

This example will remove the server permissions AlterAnyAvailabilityGroup and ViewServerState from the login 'NT AUTHORITY\SYSTEM'.

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

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        # Add permission
        SqlServerPermission 'SQLConfigureServerPermission'
        {
            Ensure               = 'Absent'
            ServerName           = 'SQLNODE01.company.local'
            InstanceName         = 'MSSQLSERVER'
            Principal            = 'NT AUTHORITY\SYSTEM'
            Permission           = 'AlterAnyAvailabilityGroup', 'ViewServerState'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}