sharepoint logoLast week, Mark and I were patching a fairly large production SharePoint 2016 farm. The farm consisted of the following servers:

  • Two Application Servers
  • Two Search Servers
  • Two Web Servers
  • Two Cache Servers
  • Two Microsoft SQL Servers

We started the usual process of taking backups, installing the patch on all servers and then ran SharePoint Configuration Wizard on the primary application server hosting SharePoint Central Admin.

In a couple of minutes, we received the following error:

An exception of type Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException was thrown. Additional exception information:

Upgrade [SearchAdminDatabase Name=SEARCH_DB] failed. (EventID:an59t)

Exception: The database principal owns a database role and cannot be dropped. The proposed new database owner is already a user or aliased in the database. (EventID:an59t)

Upgrade Timer job is exiting due to exception: System.Data.SqlClient.SqlException (0x80131904): The database principal owns a database role and cannot be dropped. The proposed new database owner is already a user or aliased in the database.

Was This a Known Issue?

We started looking around to see if this was a known issue and for any possible solutions. We came across Microsoft Support’s posting on the July 10 SharePoint security patch and lo and behold, it was updated with this exact known issue.

It suggested the following workaround:

SharePoint Install Patch screenshowThere were issues with this approach because first, we cannot change the owner of a database role using SQL Server Configuration Server (it’s simply not built for that) and second, there are multiple databases involved with the Search application. You can see the various DBs associated with the farm here:

list of servers screenshot

The Fix

To fix the issue for our configuration, we did the following steps:

  1. Reboot the farm machines after installing the patch, even if they don’t ask you specifically. (Because we faced later issues that required a reboot.)
  2. Open SQL Server Management Studio (SSMS) and connect to the database server used by the SharePoint Farm. Ensure that you have enough privileges.
  3. Create a new SQL script, type in the following and then execute the script (Replace DB prefix with your actual prefix):

    USE DB_Search;
    ALTER AUTHORIZATION on ROLE::[SPSEARCHDBADMIN] to [dbo];
    USE DB_Search_AnalyticsReportingStore;
    ALTER AUTHORIZATION on ROLE::[SPSEARCHDBADMIN] to [dbo];
    USE DB_Search_CrawlStore;
    ALTER AUTHORIZATION on ROLE::[SPSEARCHDBADMIN] to [dbo];
    USE DB_Search_LinkStore;
    ALTER AUTHORIZATION on ROLE::[SPSEARCHDBADMIN] to [dbo];

  4. Run the SharePoint Configuration Wizard on the primary application server (with Central Admin) and this time, it will proceed ahead and configure things as usual.
  5. After we started the SharePoint Configuration Wizard on the other application server, we again got an error which asked us to re-run the wizard to fix the error. We did that, and it did its job of configuration successfully.

SharePoint patching can be tiring when managing large farms. We have the experience of managing all manner of SharePoint farms as part of AIS Managed Services. If you would like to offload this burden, do not hesitate to contact us!