SqlRoles.adoc

DSC Resource 'SqlRoles'

SqlRoles is used to manage the SQL Server Configuration Options on a SQL Server instance.

The SqlRole DSC resource is used to create a server role, when Ensure is set to Present, or remove a server role, when Ensure is set to Absent. The resource also manages members in both built-in and user created server roles.

When the target role is sysadmin the DSC resource will prevent the user sa from being removed. This is done to keep the DSC resource from throwing an error since SQL Server does not allow this user to be removed.

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

Requirements

• Target machine must be running Windows Server 2012 or later.

• Target machine must be running SQL Server Database Engine 2012 or later.

Table 1. Attributes of category 'SqlRoles'

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

Table 2. Attributes of category 'SqlRoles/Values'

Parameter	Attribute	DataType	Description	Allowed Values
ServerRoleName	Key	String	The name of of SQL Server Database Engine role to add or remove.
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.
Members		String[]	The members the server role should have. This parameter will replace all the current server role members with the specified members.
MembersToInclude		String[]	The members the server role should include. This parameter will only add members to a server role. Can not be used at the same time as parameter Members.
MembersToExclude		String[]	The members the server role should exclude. This parameter will only remove members from a server role. Can only be used when parameter Ensure is set to Present. Can not be used at the same time as parameter Members.
Enure		String	Determines whether the alias should be added (Present) or removed (Absent).	Present Absent

Example

SqlRoles:
  Roles:
    - ServerRoleName: sysadmin
      MembersToInclude: install
      InstanceName: MSSQLSERVER
    - ServerRoleName: AdminSqlforBI
      MembersToInclude:
        - CONTOSO\John
        - CONTOSO\Kelly
      ServerName: sqltest.company.local
      InstanceName: DSC