Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2014 Master Data Services Assigning Administrator Permissions


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

Attend a SQL Server Conference for FREE >> click to learn more


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.

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

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:

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.


Last Update:


signup button

next tip button



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.

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