Sysadmin permissions for upgrading PolicyHub database
Introduction
The following article demonstrates why a user must have the sysadmin server role granted in SQL to run DB upgrade via the Configuration Manager tool.
Disclaimer
This is not an exhaustive list and there may be other additions in future releases that require Sysadmin permissions.
Getting Started
To run the PolicyHub database upgrade script, the user running this process must have Sysadmin role defined for them:
If we revoke the Sysadmin permission for the user and then use the Configuration Manager tool to upgrade the PolicyHub database, the following errors are encountered:
This error is thrown while executing the MembershipManager_ReferenceData.sql script.
The MembershipManager_ReferenceData.sql executes the uspMM_AddReplaceAllErrMessages stored procedure whose schema again makes a call to another stored procedure, uspMM_AddReplaceErrMessage. In the script of uspMM_AddReplaceErrMessage, there exists sp_dropmessage & sp_addmessage.
The sp_dropmessage & sp_addmessage is responsible for storing/dropping custom user-defined error messages. As per Microsoft’s documentation, to execute these the user requires membership in the sysadmin or serveradmin fixed server roles.
Here are some links to Microsoft documentation for more details:
sp_dropmessage (Transact-SQL).