dsccommunity / dsccommunity/SharePointDsc
SPDatabasePermissions: New resource proposal
Nobody has claimed this yet.
- Dominant language
- C#
- Stars
- 246
- Forks
- 106
- PR merge metrics
- No merged PRs in 30d
Description
Resource proposal
Use Case
SharePoint does not set all the required database permissions on its databases. A example would be the SPDataAccess Role for the Service Application Pool Account which is missing on the Content Databases.
Also the SCOM Management Pack needs db_owner Permissions on every SPDatabase or third Party Tools like SPDocKit
Details
The resource would work similar to SPShellAdmins and gets the databases at runtime. Which would save you from adding multiple SQLServerDSC Resources.
To target the affected databases they should be selectable by
- AllDatabases
- TypeName
- WebApplication
- Service Application
Failsaves:
- There won't be a
MembersParameter. - Never exclude SPManagedAccounts from a Database
This could be extended so it takes the objects context into account, but that might be a lot of code for little benefit.
Proposed properties
Parameters
| Property | Type qualifier | Data type | Description | Default value | Allowed values |
|---|---|---|---|---|---|
| IsSingleInstance | Key | String | Specifies the resource is a single instance, the value must be 'Yes' | Yes | None |
| SQLDatabaseRole | Write | StringArray[] | Names of the SQL Database Roles | None | None |
| MembersToInclude | Write | StringArray[] | List of all accounts that must be a SQL Database Rolemember | None | None |
| MembersToExclude | Write | StringArray[] | List of all accounts that are not allowed to be a SQL Database Rolemember | None | None |
| AllDatabases | Write | Boolean | Specify if all databases must get the same config as the general config | None | None |
| TypeNames | Write | MSFT_SPDatabaseByTypeNamePermissions[] | SQL Database Rolemembership Configuration by Databases TypeNames | None | None |
| WebApplications | Write | MSFT_SPDatabaseByWebApplicationPermissions[] | SQL Database Rolemembership Configuration by SPWebApplication | None | None |
| ServiceApplications | Write | MSFT_SPDatabaseByServiceApplicationPermissions[] | SQL Database Rolemembership Configuration by SPServiceApplication | None | None |
| ExcludeDatabases | Write | StringArray[] | Specify all databases that must be excluded from AllDatabases | None | None |
MSFT_SPDatabaseByTypeNamePermissions
| Property | Type qualifier | Data type | Description | Default value | Allowed values |
|---|---|---|---|---|---|
| Name | Key | String | Full Typename of the Database | Yes | |
| SQLDatabaseRole | Write | StringArray[] | Name of the SQL Database Role | None | |
| MembersToInclude | Write | StringArray[] | List of all accounts that must be a SQL Database Rolemember | None | |
| MembersToExclude | Write | StringArray[] | List of all accounts that are not allowed to be a SQL Database Rolemember | None |
MSFT_SPDatabaseByWebApplicationPermissions
| Property | Type qualifier | Data type | Description | Default value | Allowed values |
|---|---|---|---|---|---|
| Name | Key | String | Valid Value of Type Microsoft.SharePoint.PowerShell.SPWebApplicationPipeBind |
Yes | |
| SQLDatabaseRole | Write | StringArray[] | Names of the SQL Database Roles | None | |
| MembersToInclude | Write | StringArray[] | List of all accounts that must be a SQL Database Rolemember | None | |
| MembersToExclude | Write | StringArray[] | List of all accounts that are not allowed to be a SQL Database Rolemember | None |
MSFT_SPDatabaseByServiceApplicationPermissions
| Property | Type qualifier | Data type | Description | Default value | Allowed values |
|---|---|---|---|---|---|
| Name | Key | String | Valid Value of Type Microsoft.SharePoint.PowerShell.SPServiceApplicationPipeBind |
Yes | |
| SQLDatabaseRole | Write | StringArray[] | Names of the SQL Database Roles | None | |
| MembersToInclude | Write | StringArray[] | List of all accounts that must be a SQL Database Rolemember | None | |
| MembersToExclude | Write | StringArray[] | List of all accounts that are not allowed to be a SQL Database Rolemember | None |
Special considerations or limitations
To Prevent PSConfig from removing db_owner permissions from a database the registry key BypassDboDropMember at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\16.0\WSS\must be set to one on every server.
Some details at Hinweise zum Sicherheitsupdate für SharePoint Enterprise Server 2016: 10. Oktober 2017. That key works for every SharePoint Version.
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
No implementation files or tests are named. Start by reviewing the existing SPShellAdmins resource and the proposed database-selection and permission parameters, then determine how the AllDatabases, TypeNames, WebApplications, and ServiceApplications cases should be covered while preserving the stated safeguards; done means the new resource manages the requested SQL roles and members without excluding SPManagedAccounts.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- powershell, sql
- Domain
- databases, devops
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 25/100