Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Permission Changes in Master Data Services 2016


By:   |   Read Comments   |   Related Tips: More > Master Data Services

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

With the release of SQL Server 2016, there have been some changes in the security model for groups and/or users for Master Data Services (MDS). New roles have been added and the granularity of permissions has been modified. This tip will explain those changes.

Solution

Master Data Services has received quite an upgrade in SQL Server 2016. Lots of new functionality has been added and existing routines have been improved. One of those is the set of permissions that you can assign to a user or a group. Let's talk about Administrators first.

Note: you can assign permissions to either Windows Groups and Users. In this tip, I'll just refer to Users, but know the same applies to Groups as well.

Master Data Services Administrator Roles

In previous releases, you had to choose a Windows account during the installation and configuration of the MDS database. This account would become a system-wide administrator and have access to all models. The problem is sometimes system administrators chose their own account instead of a service account. If that person leaves the company, it could break the MDS installation if the account becomes invalid. Correcting this was not easy; it requires lots of manual changes in the MDS database. In SQL Server 2016, you still have to configure such an account.

original super user

However, you can now assign users to a new role called Super User. Users assigned to this role will have administrator access to all models. This allows is to mitigate the problem with the super user assigned during configuration of MDS. Furthermore, it also allows you to create multiple super users, which can be useful when maintenance is done by a team of administrators. You can assign users to this role in the Functions tab in the editing screen of a user in the User and Group Permissions section of MDS.

assign user to super user role

Aside from the Super User Role, some permission sets have been explicitly defined. For example, when a user had update permission on a model in a previous release - and no other permissions in the subtree below the model - the user would become a model administrator. However, if at a later point in time the user gets another explicit permission assigned in the model sub tree (for example on an entity), the user would lose model administrator permissions. Now you can explicitly assign the model administrator role to a user. Any permissions assigned on a lower level are ignored.

assign user as model administrator

The same is true for entity administrators: you can now assign explicit entity administrator privileges to a user. Entity administrators play an important role in change sets, as described in the tips Approval Workflow in SQL Server Master Data Services 2016 using Change Sets - Part 1 and Part 2.

assign user as entity administrator

To recap, there are three administrator roles:

  • Super User - Access to all models and functional areas.
  • Model Administrator - If has access to explorer, the user can modify all master data. If access to system administration, the user can perform all administrative tasks on the model.
  • Entity Administrator - If has access to explorer, the user can modify all data of the entity. The user can also approve or reject change sets for the entity.

Note that making a user a Model Administrator on all the models is not the same as having the Super User permissions. A Super User has access to all the functional areas, while a Model Administrator needs explicit access to a functional area. For example, if a Model Administrator doesn't have access to the System Administration functional area, he/she cannot create new entities.

More Granular Permissions in Master Data Services

In previous versions, you could only assign two permissions on a model object: read-only or update. With MDS 2016, you can now assign the following permissions: read, create, update, delete or deny.

all model permissions

If you want to assign a user all permissions (read + create + update + delete) you can choose the shortcut for All permissions. In the tree view, indicators are used to show which permissions a user has for an entity. On the right, you can view a summary of all permissions.

model permissions summary

If you assign a user create, update or delete to an entity, the user automatically gets the read permission assigned as well. According to best practices on the MSDN website you should assign the All permission on the model, then explicitly assign other permissions on the lower level objects.

Conclusion

In SQL Server 2016, there is much more flexibility in the MDS security model. You can assign users or groups to various administrator roles and the permissions on the model objects are more fine-grained.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools