Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to configure user permissions in SQL Server Master Data Services


By:   |   Last Updated: 2016-06-23   |   Comments (1)   |   Related Tips: More > 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


Last Updated: 2016-06-23


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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???


Learn more about SQL Server tools