How to configure user permissions in SQL Server Master Data Services

By:   |   Comments (1)   |   Related: > Master Data Services


Problem

In Master Data Services data is organized by models which are composed of entities which contain members. Users need to be provisioned access on different models and entities as per their authorization level. Some users may need read-only access, some may not be allowed any access, while others may be allowed full access on the data as well as metadata. In this tip we will look at how to provision the required level of access to users on a model as well as on an entity.

Solution

In this tip we assume you have at least one model with a few entities and members. To view the differences between the levels of permissions, we will also assume you have at least two users configured in Master Data Services (MDS). If not, you can navigate to the Master Data Manager and open the User and Group permissions screen. Using the Manage Users section, an admin can add and configure users in MDS. We have two users already configured in MDS as shown below.

Edit user permissions in SQL Server Master Data Services

Select the user for which you intend to configure permissions. Click on the edit button and select the models tab. Click on the edit button on the screen to edit permissions for the model / entity / attributes. Clicking the edit button makes the model explorer pane editable on the screen. Click on the intended model / entity / attribute and you should be able to see a pop-up menu. This menu will allow you to set three types of permissions - Deny, Read-Only and Update. The permission names are self-explanatory and they set the same level of access on the data for the user.

Set permissions in SQL Server Master Data Services

Once the permissions are set, let the user access the models. The models which have a deny access configured, won't be listed at all on the Master Data Manager home page for the user. The entities which have read-only access configured will be displayed with a lock icon adjacent to the name as shown below. This means the members of these entities are read-only.

Read-only access in SQL Server Master Data Services

The entities with update access will be available for editing.

Update access in SQL Server Master Data Services
Next Steps
  • You can set permissions even on the leaf level attributes of the entity. Try to set permissions on a few attributes and test the effect when the user attempts to access the associated entity.
  • Read more Master Data Services Tips


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, January 19, 2018 - 6:12:44 AM - Jörgen H Back To Top (74993)

Hi

I really wonder how the MDS team have thought about adding users.

We will have 5000 users amd most of them are having individual rights.

With the current UI it would takes days if not weeks to onmy add the users.

Isnt there any supported way of adding users in batch???















get free sql tips
agree to terms