SQL Server Master Data Services Permissions for Subscription Views

All we need is an easy explanation of the problem, so here it is.

I have an instance of SQL Server Master Data Services installed, there are users managing the master data via the Excel add-in, now we’re ready to integrate the master data into products. We’ve created and published subscription views, but users can’t connect to the server to query from them. Our hope is to use PowerQuery in Excel to enable users to leverage the master data, so we’re trying to figure out how best to manage the permissions.

Are permissions to query subscription views managed separately from the master data permissions managed in the browser UI?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Subscription Views are intended primarily for system-to-system integration, and are controlled by ordinary SQL Server permissions, not the MDS end-user security model.

So you would need to grant SELECT on the subscription views to a group that the target users are in.

Although the subscription views are generally suitable for interactive query and extract, a common pattern is to copy the data from the subscription views to a Data Lake, Data Warehouse, or Operational Data Store for consumption.

Another common pattern is to put a layer of end-user views on top of the Subscription Views. The Subscription Views expose things like status and approval flags that you might not want to expose to reporting users.

One limitation of the subscription views is that they show the current status of each master data row, even if it’s currently failing one or more business rules. If you copy the subscription views to another location, you can maintain a "last-known-good" version of each row.

Of course you can also use model versioning to publish the validated model, but some master data changes too fast for that to be practical.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply