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”.
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:
GRANT VIEW SERVER STATE TO [yourmachine\MDSAppPool];
Now the business rule can be published without an issue.
it’s possible to logs are located at the folder C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication\MDSTempDir. This might have been changed in a later CU.