Microsoft SQL Server Master Data Services 2016 (MDS) provides comprehensive permissions configuration functionality, from the functional permissions level right down to column and row level. However there are a few gotchas that if you are aware of up front, will make your permissions configuration and maintenance life easier. Here goes’:
1. Permissions take time to persist
Due to the nature of the SQL Server Master Data Services application, there is a lag time between configuring a permission, and that permission being applied to a MDS user/group. By default, via Master Data Services Configuration Manager, the lag time is 3600 seconds (or 60 minutes!). However, you can make the permissions immediate by calling the following MDS stored procedure “[mdm].[udpSecurityMemberProcessRebuildModel]“. Well worth a look: https://msdn.microsoft.com/en-us/library/ff486978.aspx
2. Least permissive permissions rule the roost
MDS 2016 offers 5 main object permissions levels; Read, Create, Update, Delete, and Deny. The thing to note here is that least permissive permissions
rule. For example if a user is granted write access to an entity, but read access on certain rows in that entity, then the write permissions are overruled by the read permissions on the rows in question. The overlapping & overruling permissions process an related hierarhy is well worth being aware of: https://technet.microsoft.com/en-us/library/ff487009(v=sql.130).asp
3. Permissions can be inferred
Specifically speaking, you don’t have to give a user model level permissions to access an entity. If they are assigned permissions on a specific entity, as long as they have functional Explorer permissions, that user can access that entity.
In general, the above applies not only Master Data Services 2016, but also MDS 2008 R2, 2012, and 2014; the key difference being less granular object level permissions on the earlier versions. In other words, MDS 2008 R2, 2012, and 2014 provide Read / Update (Edit) / Deny permissions. As mentioned, due to the nature of the application, permissions can take longer than expected to get processed so it’s worth keeping in mind e.g. when you are testing permissions, or an end user is waiting for access over the phone. Also the overlapping permissions hierarchy can take a little getting used to.
I hope this article helps to make your MDS permissions configuration a little easier and saves you some time and headaches!