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.
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
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
DSC configuration
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
Operating system the target node is running
PowerShell version and build the target node is running
SharePointDsc version