SPUserProfileServiceApp: PSDSCRunAsCredential does not become a db_owner

[ChristophHannappel]

Problem description

When SPUserProfileServiceApp creates the Service Application it seams that the PSDSCRunAsCredential Account does not get db_owner but only SPDataAccess role membership on the Profile and Social Database.

I think the permission is given from the following code: https://github.com/dsccommunity/SharePointDsc/blob/1ee98a02e9cc34c17725c830d84bc3b4701fb50f/SharePointDsc/DSCResources/MSFT_SPUserProfileServiceApp/MSFT_SPUserProfileServiceApp.psm1#L489

While the application is running perfectly fine the resource SPShellAdmins fails with user does not have permission, because it can not add a sql user.

The PSDSCRunAsCredential has the sql server role dbcreator and securityadmin

Verbose logs

Leaving BeginProcessing Method of Add-SPShellAdmin.
User does not have permission to perform this action.
    + CategoryInfo          : InvalidData: (Microsoft.Share...AddSPShellAdmin:) [], CimException
    + FullyQualifiedErrorId : Microsoft.SharePoint.PowerShell.SPCmdletAddSPShellAdmin
    + PSComputerName        : localhost

DSC configuration

onfiguration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [PSCredential]
        $SetupAccount
    )

    Import-DscResource -ModuleName SharePointDsc

    node localhost
    {
        SPUserProfileServiceApp UserProfileServiceApp
        {
            Name                 = "User Profile Service Application"
            ApplicationPool      = "SharePoint Service Applications"
            MySiteHostLocation   = "http://my.sharepoint.contoso.local"
            MySiteManagedPath    = "personal"
            ProfileDBName        = "SP_UserProfiles"
            ProfileDBServer      = "SQL.contoso.local\SQLINSTANCE"
            SocialDBName         = "SP_Social"
            SocialDBServer       = "SQL.contoso.local\SQLINSTANCE"
            SyncDBName           = "SP_ProfileSync"
            SyncDBServer         = "SQL.contoso.local\SQLINSTANCE"
            EnableNetBIOS        = $false
            PsDscRunAsCredential = $SetupAccount
        }
    }
}

Suggested solution

Make the PSDSCRunAsCredential a db_owner on creation or update the documentation that this behaviour is due to api limits.

SharePoint version and build

SharePoint Server 2019

Operating system the target node is running

OsName               : Microsoft Windows Server 2019 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture       : 64-bit
WindowsVersion       : 1809
WindowsBuildLabEx    : 17763.1.amd64fre.rs5_release.180914-1434
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

PowerShell version and build the target node is running

| Name                      | Value                   |
|---------------------------|-------------------------|
| PSVersion                 | 5.1.17763.2268          |
| PSEdition                 | Desktop                 |
| PSCompatibleVersions      | {1.0, 2.0, 3.0, 4.0...} |
| BuildVersion              | 10.0.17763.2268         |
| CLRVersion                | 4.0.30319.42000         |
| WSManStackVersion         | 3.0                     |
| PSRemotingProtocolVersion | 2.3                     |
| SerializationVersion      | 1.1.0.1                 |

SharePointDsc version

| Name          | Version | Path                                                                              |
|---------------|---------|-----------------------------------------------------------------------------------|
| SharePointDSC | 5.1.0   | C:\Program Files\WindowsPowerShell\Modules\SharePointDSC\5.1.0\SharePointDSC.psd1 |