0

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?

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.