Changing System Administrator Account for Master Data Services–would need this after restoring MDS database to another instance of SQL Server

  • Article

When you try to restore MDS database from one server to another server in a different domain where you don’t share same system administrator account, you will need to change the internal sys admin account for MDS database after the restore is done, so that you can connect to restored DB’s MDS UI. 

This article talks about how to change that internal system administrator account residing in mdm.tblUser table.

https://msdn.microsoft.com/en-us/library/ff487048.aspx

To change the administrator account

  1. Open SQL Server Management Studio and connect to the Database Engine instance for your Master Data Services database.

  2. In mdm.tblUser, find the user that will be the new administrator and copy the value in the SID column.

  3. Create a new query.

  4. Type the following text, replacing DOMAIN\user_name with the new administrator's user name and SID with the value you copied in step 2.

EXEC [mdm].[udpSecuritySetAdministrator]

 @UserName='DOMAIN\user_name', @SID = 'SID', @PromoteNonAdmin = 1

 

Here you will need to know the SID for the account (domain\user_name).  To find out the SID, follow the instruction in this article -

https://support.microsoft.com/kb/154599 - How to Associate a Username with a Security Identifier (SID)

  1. Open Registry Editor and navigate to:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion \ProfileList

  2. Under the ProfileList key, you will see the SIDs. By selecting each one individually, you can look at the value entry and see what user name is associated with that particular SID.

After this step is performed, you should be able to open up the MDS UI that is configured successfully with restored MDS database.