Treat granting web application pool account to user profile databases using elevated Farm account privilege

[ezybzy]

I've recently setup SharePoint 2019 with limited database privilege (dbcreator, securityadmin) for setup account. During User Profile Service provision, I encountered the following error messages.

 - Granting {WebAppPool Account} rights to {User Profile Database}...
Add-SPShellAdmin :
     "Cannot add {WebAppPool Account} to the SharePoint_Shell_Access role of the database {User Profile Database}. A possible cause of this
error is that the account name was already added to the database as a login using a different user name than the account name."
At X:\SP\Automation\AutoSPInstallerModule.psm1:3371 char:78
+ ... rofileDB} | Add-SPShellAdmin -UserName $($portalAppPoolAcct.username) ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (Microsoft.Share...AddSPShellAdmin:SPCmdletAddSPShellAdmin) [Add-SPShellAdmin], ArgumentException
    + FullyQualifiedErrorId : Microsoft.SharePoint.PowerShell.SPCmdletAddSPShellAdmin

 - Granting {WebAppPool Account} rights to {User Profile Database}_Social...
Add-SPShellAdmin :
     "Cannot add {WebAppPool Account} to the SharePoint_Shell_Access role of the database {User Profile Database}_Social. A possible cause of
this error is that the account name was already added to the database as a login using a different user name than the account name."
At X:\SP\Automation\AutoSPInstallerModule.psm1:3373 char:77
+ ... socialDB} | Add-SPShellAdmin -UserName $($portalAppPoolAcct.username) ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (Microsoft.Share...AddSPShellAdmin:SPCmdletAddSPShellAdmin) [Add-SPShellAdmin], ArgumentException
    + FullyQualifiedErrorId : Microsoft.SharePoint.PowerShell.SPCmdletAddSPShellAdmin

There is one suggestion from Wouter Bleeker suggesting that the setup account should be granted to sysadmin which is impossible for my situation. I resolved this problem by running that two commands (Line 3371 and 3373) within elevated Farm account privilege which the AutoSPInstaller had already used this technique during creating User Profile Service application (the CreateUpsAsAdmin function).

Hope that this will be integrated to the master branch.

[ezybzy]

BTW, I get one issue after setup which is granting permission on User Profile Service Application "Manage User Permissions" page will lead to "Sorry, this site hasn't been shared with you.".

[UmrG]

@ezybzy , which lines you added and where in CreateUPSAsAdmin()? I have seen this issue but not in every environment. I build environment on daily basis so not sure why its happening.

did you add the following lines ?

Write-Host -ForegroundColor White " - Granting $($portalAppPoolAcct.username) rights to $mySiteDB..." Get-SPDatabase | Where-Object {$.Name -eq $mySiteDB} | Add-SPShellAdmin -UserName $($portalAppPoolAcct.username) Write-Host -ForegroundColor White " - Granting $($portalAppPoolAcct.username) rights to $profileDB..." Get-SPDatabase | Where-Object {$.Name -eq $profileDB} | Add-SPShellAdmin -UserName $($portalAppPoolAcct.username) Write-Host -ForegroundColor White " - Granting $($portalAppPoolAcct.username) rights to $socialDB..." Get-SPDatabase | Where-Object {$_.Name -eq $socialDB} | Add-SPShellAdmin -UserName $($portalAppPoolAcct.username)

If so , after what line you added them in CreateUPSAsAdmin()?

Thanks.

[UmrG]

@ezybzy , check this out : http://www.harbar.net/archive/2010/10/30/avoiding-the-default-schema-issue-when-creating-the-user-profile.aspx

[ezybzy]

@UmrG, I rerun Line 3371 and 3373 logic in an elevated Farm account priviledge (the same technique shown in CreateUPSAsAdmin function -- I've not modified the code inside CreateUPSAsAdmin function). You may create another elevated Farm account priviledge code block for those given lines (or that above if block).

From the cited Harbar post, I got the first case (Farm account is dbo). I can delete the UPS via Central Administration. After rerunning your script, I get the same error messages.

I've another minimal farm which uses the same amount of accounts except that sysadmin is given to setup account. After removing sysadmin from setup account, I've no problem using manage user permissions page.