DSCResources/DSC_SqlDatabasePermission/en-US/about_SqlDatabasePermission.help.txt
|
.NAME
SqlDatabasePermission .DESCRIPTION The `SqlDatabasePermission` DSC resource is used to grant, deny or revoke permissions for a user in a database. For more information about permissions, please read the article [Permissions (Database Engine)](https://docs.microsoft.com/en-us/sql/relational-databases/security/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. Valid permission names can be found in the article [DatabasePermissionSet Class properties](https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.databasepermissionset#properties). ## Requirements * Target machine must be running Windows Server 2012 or later. * Target machine must be running SQL Server Database Engine 2012 or later. ## 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+SqlDatabasePermission). .PARAMETER DatabaseName Key - String The name of the database. .PARAMETER Name Key - String The name of the user that should be granted or denied the permission. .PARAMETER PermissionState Key - String Allowed values: Grant, Deny, GrantWithGrant The state of the permission. .PARAMETER InstanceName Key - String The name of the SQL Server instance to be configured. Default value is 'MSSQLSERVER'. .PARAMETER Permissions Required - StringArray The permissions to be granted or denied for the user in the database. .PARAMETER Ensure Write - String Allowed values: Present, Absent If the permission should be granted ('Present') or revoked ('Absent'). .PARAMETER ServerName Write - String The host name of the SQL Server to be configured. Default value is $env:COMPUTERNAME. .EXAMPLE 1 This example shows how to ensure that the user account CONTOSO\SQLAdmin has "Connect" and "Update" SQL Permissions for database "AdventureWorks". Configuration Example { param ( [Parameter(Mandatory = $true)] [System.Management.Automation.PSCredential] $SqlAdministratorCredential ) Import-DscResource -ModuleName 'SqlServerDsc' node localhost { SqlDatabasePermission 'Grant_SqlDatabasePermissions_SQLAdmin_Db01' { Ensure = 'Present' Name = 'CONTOSO\SQLAdmin' DatabaseName = 'AdventureWorks' PermissionState = 'Grant' Permissions = @('Connect', 'Update') ServerName = 'sqltest.company.local' InstanceName = 'DSC' PsDscRunAsCredential = $SqlAdministratorCredential } SqlDatabasePermission 'Grant_SqlDatabasePermissions_SQLUser_Db01' { Ensure = 'Present' Name = 'CONTOSO\SQLUser' DatabaseName = 'AdventureWorks' PermissionState = 'Grant' Permissions = @('Connect', 'Update') ServerName = 'sqltest.company.local' InstanceName = 'DSC' PsDscRunAsCredential = $SqlAdministratorCredential } SqlDatabasePermission 'Grant_SqlDatabasePermissions_SQLAdmin_Db02' { Ensure = 'Present' Name = 'CONTOSO\SQLAdmin' DatabaseName = 'AdventureWorksLT' PermissionState = 'Grant' Permissions = @('Connect', 'Update') ServerName = 'sqltest.company.local' InstanceName = 'DSC' PsDscRunAsCredential = $SqlAdministratorCredential } } } .EXAMPLE 2 This example shows how to ensure that the user account CONTOSO\SQLAdmin hasn't "Select" and "Create Table" SQL Permissions for database "AdventureWorks". Configuration Example { param ( [Parameter(Mandatory = $true)] [System.Management.Automation.PSCredential] $SqlAdministratorCredential ) Import-DscResource -ModuleName 'SqlServerDsc' node localhost { SqlDatabasePermission 'RevokeGrant_SqlDatabasePermissions_SQLAdmin' { Ensure = 'Absent' Name = 'CONTOSO\SQLAdmin' DatabaseName = 'AdventureWorks' PermissionState = 'Grant' Permissions = @('Connect', 'Update') ServerName = 'sqltest.company.local' InstanceName = 'DSC' PsDscRunAsCredential = $SqlAdministratorCredential } SqlDatabasePermission 'RevokeDeny_SqlDatabasePermissions_SQLAdmin' { Ensure = 'Absent' Name = 'CONTOSO\SQLAdmin' DatabaseName = 'AdventureWorks' PermissionState = 'Deny' Permissions = @('Select', 'CreateTable') ServerName = 'sqltest.company.local' InstanceName = 'DSC' PsDscRunAsCredential = $SqlAdministratorCredential } } } .EXAMPLE 3 This example shows how to ensure that the user account CONTOSO\SQLAdmin has "Connect" and "Update" SQL Permissions for database "AdventureWorks". Configuration Example { param ( [Parameter(Mandatory = $true)] [System.Management.Automation.PSCredential] $SqlAdministratorCredential ) Import-DscResource -ModuleName 'SqlServerDsc' node localhost { SqlDatabasePermission 'Deny_SqlDatabasePermissions_SQLAdmin_Db01' { Ensure = 'Present' Name = 'CONTOSO\SQLAdmin' DatabaseName = 'AdventureWorks' PermissionState = 'Deny' Permissions = @('Select', 'CreateTable') ServerName = 'sqltest.company.local' InstanceName = 'DSC' PsDscRunAsCredential = $SqlAdministratorCredential } SqlDatabasePermission 'Deny_SqlDatabasePermissions_SQLUser_Db01' { Ensure = 'Present' Name = 'CONTOSO\SQLUser' DatabaseName = 'AdventureWorks' PermissionState = 'Deny' Permissions = @('Select', 'CreateTable') ServerName = 'sqltest.company.local' InstanceName = 'DSC' PsDscRunAsCredential = $SqlAdministratorCredential } SqlDatabasePermission 'Deny_SqlDatabasePermissions_SQLAdmin_Db02' { Ensure = 'Present' Name = 'CONTOSO\SQLAdmin' DatabaseName = 'AdventureWorksLT' PermissionState = 'Deny' Permissions = @('Select', 'CreateTable') ServerName = 'sqltest.company.local' InstanceName = 'DSC' PsDscRunAsCredential = $SqlAdministratorCredential } } } |