I spent quite a bit of time today troubleshooting a very annoying issue with MDS, which turned out to be a total user error. For the longest time I could not figure out why my MDS users could not connect to my MDS models. The confusion stems from the fact that even though you can assign a user to such functions as Integration Management and System Administration, that in of itself does not grant him or her access to any models, which is a little counter intuitive (but not really) if you don’t think about it. I guess I had spent a few days working with SQL Server security and I am kind of used to the fact that if I assign a user to a sys admin role in SQL Server, the user automatically gets access to all databases (and objects). In Master Data Services, assigning a user to a System Administration Function is NOT synonymous to making this user a System Administrator. The only thing that it really means is that this module (in this case System Administration) simply becomes an available option in the MDS Explorer application. So in order to grant access to a model or entities, the administrator has to do the following:
- Go to “User and Group Permissions”
- Click on the user who needs to be granted access to the model and click on Edit button above or select Edit from the option available by clicking on a little drop down in the very first column of the user row
- Click on Models Tab (or drop down option if you go not through the menu above by via the drop down in the first column)
- CLICK EDIT AGAIN!!! Otherwise, when you click on the model objects below, the options to give update rights are grayed out
- Now you can click on a model and specify the leve of access you would like to grant to that user.
Here is a link to the Microsoft site that has a good overview of the Master Data Services security concepts.
2 thoughts on “Configuring Security for Microsoft Master Data Services in SQL Server 2012”
sigh…. “4. CLICK EDIT AGAIN!!!”
wasted maybe 30 minutes on trying to figure out why another user couldn’t connect. Thanks!
I second that. Very unintuitive interface. Thansk for this. Even Technet does not have any help this …
Thank you for this..