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.
Comments
Anonymous
December 30, 2014
Can we add multiple admins in MDS. If yes how can add multiple admins. If not how to add users in different roles.Anonymous
December 30, 2014
System Admin can be only one.... once you assign System admin role to other user previous one will not be admin... you can assign different roles to users which can make them sort of admin. Also system admin by default is user who installed MDS. -DilkushAnonymous
April 09, 2016
Nice blog DK! This turned out to useful for me :)- Anonymous
August 02, 2016
send some money to my account Muthar!!! :P
- Anonymous