SqlServerDsc

15.0.0

DSCResources/DSC_SqlPermission/en-US/about_SqlPermission.help.txt

.NAME
    SqlPermission
 
.DESCRIPTION
    The `SqlPermission` DSC resource sets server permissions for 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](https://github.com/dsccommunity/SqlServerDsc/issues?q=is%3Aissue+is%3Aopen+in%3Atitle+SqlPermission).
 
.PARAMETER InstanceName
    Key - String
    The name of the SQL Server instance to be configured.
 
.PARAMETER ServerName
    Write - String
    The host name of the SQL Server to be configured. Default value is $env:COMPUTERNAME.
 
.PARAMETER Ensure
    Write - String
    Allowed values: Present, Absent
    If the permission should be present or absent. Default value is 'Present'.
 
.PARAMETER Principal
    Key - String
    The login to which permission will be set.
 
.PARAMETER Permission
    Write - StringArray
    Allowed values: ConnectSql, AlterAnyAvailabilityGroup, ViewServerState, AlterAnyEndPoint
    The permission to set for the login.
 
.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
        SqlPermission 'SQLConfigureServerPermission-SYSTEM'
        {
            Ensure = 'Present'
            ServerName = 'SQLNODE01.company.local'
            InstanceName = 'MSSQLSERVER'
            Principal = 'NT AUTHORITY\SYSTEM'
            Permission = 'AlterAnyAvailabilityGroup', 'ViewServerState'
 
            PsDscRunAsCredential = $SqlAdministratorCredential
        }
 
        SqlPermission '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
        SqlPermission 'SQLConfigureServerPermission'
        {
            Ensure = 'Absent'
            ServerName = 'SQLNODE01.company.local'
            InstanceName = 'MSSQLSERVER'
            Principal = 'NT AUTHORITY\SYSTEM'
            Permission = 'AlterAnyAvailabilityGroup', 'ViewServerState'
 
            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}