SPUserProfileServiceApp: PSDSCRunAsCredential does not become a db_owner

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 |

Start at SharePointDsc/DSCResources/MSFT_SPUserProfileServiceApp/MSFT_SPUserProfileServiceApp.psm1 around line 489 and review how PSDSCRunAsCredential permissions are assigned when the service application is created or updated. Reproduce with the supplied SharePoint Server 2019 configuration and verify whether the account receives db_owner on the Profile and Social databases; done means correcting that behavior or documenting the API limitation.