When upgrading from Master Data Services 2012 to 2019 the upgrade wizard crashes as the database objects are not compatible. So you will need to export the model and import to a new instance.
Add a panic attack when you realise that the consultant that set up MDS is not availible anymore and you dont have any access to the mds service.
The first thing to do is to create a login for you user in the master data managment database. To find your SID just open command prompt and type
Then execute the following in the MDM database to create a user.
EXEC mdm.udpUserSave 1, NULL, ‘S-1-5-21-xxxx’,’DOMAIN\user’, 1, ‘Joe User’,’Marketing Manager’,’email@example.com’
Then give that user full permissions (mdm administrator) rights.
DECLARE @v_UserName NVARCHAR(100) , @v_SID NVARCHAR(250) -- new MDS Admin SET @v_UserName = 'DOMAIN\user' -- 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;
And now you own the application – You remembered to backup the database, right?
Now, if you are on mdm 2019 the [mdm].[udpSecuritySetAdministrator] stored procedure is not in the database anymore. What it does is simple enough so you can run the script from an older one. Or just create it in the database from older versions, the world might end, there might be plagues starting but it will work.
Now find the name of the mds service by starting CMD with ‘Run as admin’ and typeing
C:\Program Files\Microsoft SQL Server\1x0\Master Data Services\Configuration folder
The model names are in the [mdm].[tblModel] table and then you export the models like this
MDSModelDeploy deploynew -package c:\temp\mds\planning.pkg -model „model“ -service mds1
Then you set your permissions and you are off.