MDS Admin user no longer exist, what now?

Consider this scenario, your organisation started using SQL MDS (Master data Service) and at this point there is only one developer who is working on it and that same developer is admin on MDS.

Now if this user leaves company or somehow his account moves from one domain to another and he has not added anyone else as MDS users.

So  now everyone will get “Access Denied” when they try to access MDS website as they are not added as MDS user, MDS Admin no longer exist so basically MDS is gone for the toss and no one can do anything with it.

If you have backup of models then you can create new MDS DB and start deploying those models but what if you have too many huge models or if you don’t have backup for models.

In such scenarios we have another way which is not for light hearted people… :P

Let’s see how we can get our MDS back.

 

In MDS Db in SQL we have few tables and stored proc which will help us in doing this.

Table which is useful to us:

Mdm.tblUser

Stored proc which will help us:

Mdm.udpUserSave

Mdm.udpSecuritySetAdministrator

 

We will add new user to User table and then make that user Admin.

For adding that user in Users table we will need SID, email ID, friendly name and Domain Name of that user.

We can get this details from DC or we can create one new MDS DB under this user and get all this details from Users table of this new MDS DB.

Assume that I got below details from this new temporary DB and I want to add this user to my already existing MDS DB:

 ID MUID Status_ID SID UserName DisplayName Description EmailAddress LastLoginDTM EnterDTM EnterUserID LastChgDTM LastChgUserID
 
 ----------- ------------------------------------ --------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- --------------------------- --------------------------- ----------- --------------------------- -------------
 
 1 E4E5738E-BBBF-4D1E-917B-40811155E57E 1 S-1-5-21-2146773085-*********-*********-******** XYZ\ABC ABC LNAME NULL ABC@XYZ.COM 2014-04-21 21:32:31.900 2014-02-11 15:49:56.560 0 2014-02-11 15:50:01.297 1
 
 

 

Highlighted section will be useful for running stored proc which will add user.

 

Query to add user:

 USE [MDS]
 
 GO
 
 
 
 DECLARE @RC int
 
 DECLARE @SystemUser_ID int
 
 DECLARE @User_ID int
 
 DECLARE @SID nvarchar(250)
 
 DECLARE @UserName nvarchar(100)
 
 DECLARE @Status_ID int
 
 DECLARE @DisplayName nvarchar(256)
 
 DECLARE @Description nvarchar(500)
 
 DECLARE @EmailAddress nvarchar(100)
 
 DECLARE @Return_ID int
 
 
 
 -- TODO: Set parameter values here.
 
 set @SystemUser_ID = 2
 
 set @User_ID = 2
 
 set @SID='S-1-5-21-2146773085-*********-*********-********'
 
 set @Username= 'XYZ\ABC'
 
 set @Status_ID = 1
 
 set @DisplayName = 'ABC LNAME'
 
 set @Description = ''
 
 set @EmailAddress = 'ABC@XYZ.COM'
 
 
 
 EXECUTE @RC = [mdm].[udpUserSave]
 
 @SystemUser_ID
 
 ,@User_ID
 
 ,@SID
 
 ,@UserName
 
 ,@Status_ID
 
 ,@DisplayName
 
 ,@Description
 
 ,@EmailAddress
 
 ,@Return_ID OUTPUT
 
 GO

 

 

 

After that when you query Users table you will see this new entry being added there:

 4 A6DAA30F-CA91-4442-9166-A25674190A7F 1 S-1-5-21-2146773085-*********-*********-******** XYZ\ABC ABC LNAME ABC@XYZ.COM NULL 2014-05-27 19:27:27.307 2 2014-05-27 19:27:27.307 2
 
 

Now we have to make this user as new admin for which other query will help

 USE [MDS]
 
 GO
 
 
 
 DECLARE @RC int
 
 DECLARE @UserName nvarchar(100)
 
 DECLARE @SID nvarchar(250)
 
 DECLARE @DisplayName nvarchar(256)
 
 DECLARE @Description nvarchar(500)
 
 DECLARE @EmailAddress nvarchar(100)
 
 DECLARE @PromoteNonAdmin bit
 
 
 
 set @UserName = 'XYZ\ABC'
 
 set @SID = 'S-1-5-21-2146773085-*********-*********-********'
 
 set @DisplayName = 'ABC LNAME'
 
 set @EmailAddress = 'ABC@XYZ.COM'
 
 set @PromoteNonAdmin = 1
 
 set @Description = ''
 
 
 
 EXECUTE @RC = [mdm].[udpSecuritySetAdministrator]
 
 @UserName
 
 ,@SID
 
 ,@DisplayName
 
 ,@Description
 
 ,@EmailAddress
 
 ,@PromoteNonAdmin
 
 GO
  

Once we make this user as admin it will delete other admin user present in Users table and after that we can login using this new user and then add new users as various roles so that we don’t run into same issue again.

Also make sure we change MDS DB to old DB again once we are done with adding user.

I hope this will help few people in getting out of such difficult situation as most of time when this happens we will be deep trouble as we have to re do all work done so far.

That’s all from me at this point, let me know if anyone has any questions.