SQL Server 2014 Master Data Services Assigning Administrator Permissions

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

Problem

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.

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

Solution

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:

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:

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.

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

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:

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:

USE MDS
GO

-- find current MDS System Administrator; ID will be always "1"
SELECT UserName FROM  mdm.tblUser WHERE ID = 1;
  
DECLARE @v_UserName NVARCHAR(100), @v_SID NVARCHAR(250)

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

About the author

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