SPDatabasePermissions: New resource proposal

[ChristophHannappel]

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 Members Parameter.
• 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.

[andikrueger]

Did you have a chance to evaluate SQLServerDSC for this use-case: https://github.com/dsccommunity/SqlServerDsc/wiki/SqlDatabasePermission

[ykuijs]

On one hand, I like this idea. But on the other hand, I agree with @andikrueger that we should not recreate functionality from other modules. Especially since adding users to roles can mean we also have to add a login in SQL, which requires permissions within the SQL instance. A better solution is to use DSC and SQLServerDsc to update the permissions in SQL.

Do keep in mind that SQLServerDsc is created to run on the SQL Server itself and you can run into limitations when trying to configure SQL from a SharePoint server. For more info about these issues, see here.

NOTE: Also check out the SQLDatabaseRole resource.

[ChristophHannappel]

I understand the concern about recreating the functionality. While the SQLServerDSC has the resources, the implementation adds a lot of resources in a medium sized farm - I'll try to solve this with the DSCWorkshop framework and also add a feature request for Subscription Edition to expose the existing Methods for SQL Role Memberships.

There might be a middleground solution for SPDataAccess on Content Databases of Webapplication which would fix the usual Service Application Pool Account permission errors within SharePoint itself without replicating code.

The SPWebApplication has a GrantAccessToProcessIdentity method which adds the SPDataAccess permissions on all Content Dabases of a WebApplication. Details SPWebApplication.GrantAccessToProcessIdentity method (String, SPPolicyRoleType)

$spWebApplication = Get-SPWebApplication -Identity <WebappName>
$spWebApplication.GrantAccessToProcessIdentity('DOMAIN\User')

I didn't find a public method on the SPDatabase class to check if a user is a rolemember, do you have any idea if there is a way?