SQL Server 2014 Master Data Services Assigning Administrator Permissions

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


We are using SQL Server 2014 Master Data Services (MDS). I am a MDS System Administrator and I assigned another user (MDS Admin\DBA) all of the functions in MDS as shown below.

MDS permissions

When this person connects to the Master Data Manager Web Application he cannot see any models as shown below. What is the problem?

MDS Models not available

Let's take a look at this issue.

Granting System Administration and other permissions in Master Data Services

To assign permissions to another user we need to use the "User and Group Permissions" menu in the Master Data Manager Web Application:

MDS permissions menu

Read this tip about creating a user and assigning the permissions.

Click on the user's name and use the "Edit selected user" button to edit the permissions:

MDS user's permissions edit

To make sure the user can view and edit other models we need to grant permissions on each model:

  • Click on the "Models" tab.
  • Select specific model or leave "All" under "Model" drop down menu. If you leave "All" models (default) you will still have to grant permissions to each model one by one.
  • Right click on the model's name and select permission you want to grant to the user.
MDS model permissions

Now our MDS Admin can see the "DEMO_Model" and make changes to it:

MDS model displayed

Why MDS Admin that has System Administrator permissions cannot see all models?

The question is: "Why does a MDS Admin that has System Administrator permissions assigned cannot see all of the models?"  The assumption is that the System Administrators should have full access to everything.

As per the Microsoft TechNet article "There is only one Master Data Services system administrator. The system administrator is the user specified for the Administrator Account when you create the Master Data Services database".

Here are the steps that we usually take when we create a new MDS database:

  1. In MDS Configuration Manager click on the "Database Configuration" on the left side menu
  2. Click the "Create Database" button
  3. After you entered Database Server and Database configuration settings you will be able to specify the Administrator Account:
Default MDS System Administrator

Change the MDS System Administrator Account

To change the MDS System Administrator Account you can follow these steps provided by Microsoft or use the script below:

-- find current MDS System Administrator; ID will be always "1" SELECT UserName FROM mdm.tblUser WHERE ID = 1;
-- new MDS Admin SET @v_UserName = 'DOMAIN\MDS_Admin_2'
-- find the new MDS Admin's SID SELECT @v_SID = [SID] FROM mdm.tblUser WHERE UserName = @v_UserName;
-- Set the new MDS System Administrator EXEC [mdm].[udpSecuritySetAdministrator] @UserName=@v_UserName, @SID = @v_SID, @PromoteNonAdmin = 1;

The MDS System Administrator will always have ID=1 in the mdm.tblUser table.

In our example, in the script above we assume that the new MDS Admin already has a user account in MDS.

Note: The old MDS System Administrator will be deleted completely.

Another case when you need to run the stored procedure above is when you restore an MDS database to a different environment that has a different MDS System Administrator account or which is in a different domain. Read this article that has more details.

Single MDS Administrator solved in SQL Server 2016

It's not very convenient to just have one account that has access and it's always good to have a backup admin to support an environment.  Also, it is not a good security practice to share a single MDS Admin account and you are not able to use user groups.

The good news is that in SQL Server 2016 Microsoft has made a lot of security improvements including the new "Master Data Services Super User" administrator type.  Now Super User permissions can be assigned to multiple users and/or groups.

Next Steps
  • Read other MDS tips here.
  • Read about SQL Server 2014 MDS Security here.
  • Read about SQL Server 2016 MDS Security here.
  • Find out what's new in SQL Server 2016 MDS.
  • Check Microsoft resources about MDS.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Svetlana Golovko Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

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

Monday, December 7, 2020 - 6:04:27 PM - Svetlana Back To Top (87890)
You will need to assign a user the "SuperUser" function using Master Data Services web administration tool. Here is some info: https://docs.microsoft.com/en-us/sql/master-data-services/administrators-master-data-services?view=sql-server-2016#master-data-services-super-user

Friday, December 4, 2020 - 10:02:37 PM - Ademir Back To Top (87885)
Hello! Thanks for replying! Iím using 2016! So, is there another way to change user now? Iíve tried to change on IIS and it gave me errors regarding access on some folders :(

Thursday, December 3, 2020 - 6:23:41 PM - Svetlana Back To Top (87880)
What version of MDS do you use? The udpSecuritySetAdministrator procedure is not available anymore in the newer versions of SQL Server (MDS).

Thursday, December 3, 2020 - 4:21:23 PM - Ademir O Passos Back To Top (87878)
Hello! I'm receiving error: Could not find stored procedure 'mdm.udpSecuritySetAdministrator'. Any idea what is going on? Thanks!

get free sql tips
agree to terms