SqlPermissions.adoc

DSC Resource 'SqlPermissions'

SqlPermissions is used to grant, deny or revoke Server permissions for a login. 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.

Source	https://github.com/dsccommunity/CommonTasks/tree/main/source/DSCResources/SqlPermissions
DSC Resource	SqlServerDsc
Documentation	SqlServerDsc Wiki SqlPermission

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.

Table 1. Attributes of category 'SqlPermissions'

Parameter	Attribute	DataType	Description	Allowed Values
Values	Mandatory	Hashtable[]	List of SQL permissions.

Table 2. Attributes of category 'SqlPermissions/Values'

Parameter	Attribute	DataType	Description	Allowed Values
InstanceName	Key	String	The name of the SQL Server instance to be configured.
ServerName		String	The host name of the SQL Server to be configured. Default value is the current computer name.
Enure		String	Determines whether the alias should be added (Present) or removed (Absent).	Present (default) Absent
Principal	Key	String	The login to which permission will be set.
Permission	Mandatory	Hashtable[]	The permission to set for the login.

Table 3. Attributes of category 'SqlPermissions/Values/Permission'

Parameter	Attribute	DataType	Description	Allowed Values
Grant		String[]	Permission state Grant All valid permission names can be found in the article ServerPermissionSet Class properties.	ConnectSql AlterAnyAvailabilityGroup ViewServerState AlterAnyEndPoint …​
GrantWithGrant		String[]	Permission state GrantWithGrant 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. All valid permission names can be found in the article ServerPermissionSet Class properties.	ConnectSql AlterAnyAvailabilityGroup ViewServerState AlterAnyEndPoint …​
Deny		String[]	Permission state Deny All valid permission names can be found in the article ServerPermissionSet Class properties.	ConnectSql AlterAnyAvailabilityGroup ViewServerState AlterAnyEndPoint …​

Example

SqlPermissions:
  Values:
    - Name: NT SERVICE\ClusSvc$
      InstanceName: MSSQLSERVER
      Permission:
        Grant:
          - AlterAnyAvailabilityGroup
          - ViewServerState
          - ConnectSql
        GrantWithGrant:
          - ViewServerState
          - ConnectSql
    - Name: contoso\dbadmin
      InstanceName: MSSQLSERVER
      ServerName: localhost
      Permission:
        Grant:
          - AlterAnyEndPoint
        GrantWithGrant:
          - ViewServerState
          - ConnectSql
        Deny:
          - AlterAnyAvailabilityGroup
      PermissionToInclude:
        Grant:
          - AlterAnyEndPoint
        GrantWithGrant:
          - ViewServerState
          - ConnectSql
        Deny:
          - AlterAnyAvailabilityGroup
      PermissionToExclude:
        Grant:
          - AlterAnyEndPoint
        GrantWithGrant:
          - ViewServerState
          - ConnectSql
        Deny:
          - AlterAnyAvailabilityGroup