forked from dsccommunity/SqlServerDsc
-
Notifications
You must be signed in to change notification settings - Fork 2
SqlPermission
johlju edited this page Jun 19, 2020
·
1 revision
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 |
The SqlPermission
DSC resource sets server permissions for a user
(login).
Note: Currently the resource only supports ConnectSql, AlterAnyAvailabilityGroup, AlterAnyEndPoint and ViewServerState.
- 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.
All issues are not listed here, see here for all open issues.
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
}
}
}
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
}
}
}
- 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