Master Data Services–Business Rule Activation error and its fix

While preparing for a demonstration of SQL Server 2008 R2 Master Data Services – vanilla build – my colleague Lee Theofanis from Decision Resources came across an issue regarding business rule activation.

The Issue

After creating a business rule through the Master Data Manager portal, and then trying to activate it results in the following error - A database error has occurred. Contact your system administrator. See below screenshot.



After investigation by my colleague Lee, the issue occurs due to the incorrect permission settings for the Master data services application pool identity. 

The application pool identity that is created for the MDS application is set to have “mds_exec” database role.  There is currently a problem where the business rule publication process requires sysadmin or db_owner role.

This is a known issue that is being fixed in the first cumulative update for MDS that should be available shortly.

Follow the steps below as a workaround.

a) In IIS, look and see which user is specified for the application pool Identity:

b) In SQL Server Management Studio, find this same user under the MDS database > Security> Users


c) Right-click and make sure that the user has db_owner privileges on the database.