SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Master Data Services – Database Error has Occured when Publishing Business Rule

I was preparing a demo for a session about the new features of Master Data Services in SQL Server 2016. In that demo, I created a business rule to test out the extension with custom scripts. When the business rule was finished, I tried to publish it. But, I was greeted with the following error: “300 – A database error has occurred. Contact your system administrator”.

error when publishing business rule

Not the most helpful error message, especially when you are the admin yourself. The MDS team promised that in SQL Server 2016, troubleshooting would be easier. So I went and checked out their blog post What’s New in Master Data Services – SQL2016 CTP2 (May) Release, where they have a section Improved Troubleshooting and Logging.  There it is mentioned the web application logs to the following folder: C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication. In the log file over there, I could find the following – actual – error message:

VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’

Apparently during the publishing of a business rule, MDS wants to run a stored procedure called udpValidationIsRunning. This procedure wants to check the system view sys.dm_broker_activated_tasks and apparently the necessary permissions are lacking.

The fix is luckily quite easy: just run the following statement to grant the permission to the MDSAppPool security group:

USE MASTER;
GRANT VIEW SERVER STATE TO [yourmachine\MDSAppPool];

Now the business rule can be published without an issue.

Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at element61, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

Comments

Leave a comment on the original post [sqlkover.com, opens in a new window]

Loading comments...