You will notice when setting up MDS for the first time that it will use your AD account by default. This can become a problem if you leave a company and you are the System Administrator for MDS. When your AD account gets disabled by your workplace MDS will stop working, so best to set a service account as System Administrator on MDS.
A warning before you do this, once you have changed the System Administrator, the previous one’s account will be deleted off MDS.
Prerequisites:
- You must first add the new administrator’s user name to the Master Data Manager Users list.
- You must have permission to view mdm.tblUser and to execute the mdm.udpSecurityMemberProcessRebuildModel stored procedure in the Master Data Services database.
To change the administrator account:
- Open SQL Server Management Studio and connect to the Database Engine instance for your Master Data Services database.
- In mdm.tblUser, find the user that will be the new administrator and copy the value in the SID column.
- Create a new query.
- 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
- Run the query.
After executing the query the new administrator will be in place and the previous administrators account will be removed.
Hi Jeanne,
Do we need to consider anything in terms of changing the application pool identity in IIS?
Thanks,
Hi,
You will need to check if the application pool is using your AD account as its identity and change that to a system account as well if it is being used.
Well, it looks like this is not relevant to MDS 2016 as that stored proc doesn’t exist on there at all. It’s now done through the front end using the Super User function in User and Group Permissions section (Functions tab when editing a user).
Interesting way of doing things.
I have to admit I have wrangled using Service Accounts with MDS for quite a while (SQL Server 2016) – to get service accounts to work (and more importantly, having access to be able to log into the UI!) I find that I need to create either the MDS Database or the MDS application using my own account, get it working, then switch over to the service account. If I attempt to create the application straight up using the service account, I am generally greeted with a very stark and annoying “Access is denied” error message.
It took me a few moments to realise that the reason I can’t log into the UI was because I wasn’t a user in the system.
Am I the only one who finds this rather annoying? I have a preference to setup things from the beginning using the appropriate and assigned service accounts, in the same way you would a new SQL Server instance.
I wish Microsoft would try a touch harder with this otherwise useful tool.
I actually had the same issue not so long ago with setting up the DB with the service account first. Luckily I was able to log in with the service account and add myself as a super user.
Would be nice if you could add users from the config manager as well.
Thanks Jeanne! At least I am not the only one who has had issues with this.
And yes, I agree – all they need is the ability to create a user in the Configuration app and all this angst would go away (you would not believe how much hassle I get from my audit and security team over having personal accounts attached to applications).
Cheers