-
Notifications
You must be signed in to change notification settings - Fork 2
SqlServerRole
Parameter | Attribute | DataType | Description | Allowed Values |
---|---|---|---|---|
ServerRoleName | Key | String | The name of of SQL role to add or remove. | |
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 | An enumerated value that describes if the server role is added (Present) or dropped (Absent). Default value is 'Present'. | Present, Absent |
Members | Write | StringArray[] | The members the server role should have. This parameter will replace all the current server role members with the specified members. | |
MembersToInclude | Write | StringArray[] | 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 | Write | StringArray[] | 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. |
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.
For more information about server roles, please read the below articles.
- Target machine must be running Windows Server 2012 or later.
- Target machine must be running SQL Server Database Engine 2012 or later.
All issues are not listed here, see here for all open issues.
This example shows how to ensure that both the server role named MyServerRole1 and MyServerRole2 is present on instance 'sqltest.company.local\DSC'.
Configuration Example
{
param
(
[Parameter(Mandatory = $true)]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
node localhost
{
SqlServerRole 'Add_ServerRole_MyServerRole1'
{
Ensure = 'Present'
ServerRoleName = 'MyServerRole1'
ServerName = 'sqltest.company.local'
InstanceName = 'DSC'
PsDscRunAsCredential = $SqlAdministratorCredential
}
SqlServerRole 'Add_ServerRole_MyServerRole2'
{
Ensure = 'Present'
ServerRoleName = 'MyServerRole2'
ServerName = 'sqltest.company.local'
InstanceName = 'DSC'
PsDscRunAsCredential = $SqlAdministratorCredential
}
}
}
This example shows how to ensure that the server role named serverRoleToDelete is not present on instance sqltest.company.local\DSC.
Configuration Example
{
param
(
[Parameter(Mandatory = $true)]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
node localhost
{
SqlServerRole 'Remove_ServerRole'
{
Ensure = 'Absent'
ServerRoleName = 'serverRoleToDelete'
ServerName = 'sqltest.company.local'
InstanceName = 'DSC'
PsDscRunAsCredential = $SqlAdministratorCredential
}
}
}
This example shows how to ensure that the server role named AdminSqlforBI is present on instance sqltest.company.local\DSC and only logins CONTOSO\SQLAdmin and CONTOSO\SQLAdminBI are members of this role.
Configuration Example
{
param
(
[Parameter(Mandatory = $true)]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
node localhost
{
SqlServerRole 'Add_ServerRole_AdminSqlforBI'
{
Ensure = 'Present'
ServerRoleName = 'AdminSqlforBI'
Members = 'CONTOSO\SQLAdmin', 'CONTOSO\SQLAdminBI'
ServerName = 'sqltest.company.local'
InstanceName = 'DSC'
PsDscRunAsCredential = $SqlAdministratorCredential
}
}
}
This example shows how to ensure that the server role named AdminSqlforBI is present on instance sqltest.company.local\DSC and logins CONTOSO\John and CONTOSO\Kelly are added as members of this role.
Configuration Example
{
param
(
[Parameter(Mandatory = $true)]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
node localhost
{
SqlServerRole 'Add_ServerRole_AdminSqlforBI'
{
Ensure = 'Present'
ServerRoleName = 'AdminSqlforBI'
MembersToInclude = 'CONTOSO\John', 'CONTOSO\Kelly'
ServerName = 'sqltest.company.local'
InstanceName = 'DSC'
PsDscRunAsCredential = $SqlAdministratorCredential
}
}
}
This example shows how to ensure that the server role named AdminSqlforBI is present on instance sqltest.company.local\DSC and logins CONTOSO\Mark and CONTOSO\Lucy are not members of this role.
Configuration Example
{
param
(
[Parameter(Mandatory = $true)]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
node localhost
{
SqlServerRole 'Drop_ServerRole_AdminSqlforBI'
{
Ensure = 'Present'
ServerRoleName = 'AdminSqlforBI'
MembersToExclude = 'CONTOSO\Mark', 'CONTOSO\Lucy'
ServerName = 'sqltest.company.local'
InstanceName = 'DSC'
PsDscRunAsCredential = $SqlAdministratorCredential
}
}
}
- SqlAG
- SqlAGDatabase
- SqlAgentAlert
- SqlAgentFailsafe
- SqlAgentOperator
- SqlAGListener
- SqlAGReplica
- SqlAlias
- SqlAlwaysOnService
- SqlConfiguration
- SqlDatabase
- SqlDatabaseDefaultLocation
- SqlDatabaseMail
- SqlDatabaseObjectPermission
- SqlDatabaseOwner
- SqlDatabasePermission
- SqlDatabaseRecoveryModel
- SqlDatabaseRole
- SqlDatabaseUser
- SqlEndpoint
- SqlEndpointPermission
- SqlLogin
- SqlMaxDop
- SqlMemory
- SqlPermission
- SqlProtocol
- SqlProtocolTcpIp
- SqlReplication
- SqlRole
- SqlRS
- SqlRSSetup
- SqlScript
- SqlScriptQuery
- SqlSecureConnection
- SqlServerConfiguration
- SqlServerDatabaseMail
- SqlServerEndpoint
- SqlServerEndpointPermission
- SqlServerEndpointState
- SqlServerLogin
- SqlServerMaxDop
- SqlServerMemory
- SqlServerNetwork
- SqlServerPermission
- SqlServerProtocol
- SqlServerProtocolTcpIp
- SqlServerReplication
- SqlServerRole
- SqlServerSecureConnection
- SqlServiceAccount
- SqlSetup
- SqlWaitForAG
- SqlWindowsFirewall