locked
(edit) How to restore control of a model in MDS 2014 RRS feed

  • Question

  • Hello,

    Here is the situation:

    My user account has all 5 functions that can be seen in MDS Web UI, including system administrator. I have a test model that I created and granted another system administrator (same rights) permission to update.

    In order to test permissions on attribute level I went into one of the entities in the model and tried to deny myself permission on Code attribute for that entity. After that I got an error saying "The user does not have the permission to perform the operation".

    Now I cannot see my model in the "model" tab of permissions for users/groups. It is also gone from "System administration" area so I cannot change the structure of the model.

    The same happened for the second system administrator. So right now nobody can change permissions for my model or its structure. I can still see the model in both "Explorer" and Excel add-in and can work with all entities, including the one I tried to deny myself permission on.

    Questions:

    1. How can I get back the fulls rights on this model, to able to change permissions/structure?

    2. Do I have some special permissions on created model that other systadmins do not have? If so how can I give these permissions to someone else?

    Edit:

    After some research I found this in [mdm].[viw_SYSTEM_SECURITY_USER_MODEL] in MDS database

    -- Determine Model effective permissions.  In order for a user to be a model administrator he  
    -- must have total update permission for all model metadata components and all member data.  If    
    -- read only and/or update member permissions exists for hierarchy parent nodes then those nodes   
    -- are the only ones the user is allowed to see, thus the other nodes are restricted.  In this  
    -- case the user would not be a model administrator.  The only exception to this is if the member   
    -- permission is update on the root node.

    Now I understand why this happened. If I deny myself a permission on something inside the model I lose model admin rights. I also remember now testing the deny for the second system administrator on this model too, so he is no longer model admin I guess. Too bad this was not mentioned in documentation...

    Question still stands, how do I get back control of the model if there is no administrator left for it?

    • Edited by Alex_404 Friday, March 4, 2016 10:13 AM
    Friday, March 4, 2016 9:43 AM

Answers

  • Hi Alex,

    You can get Administrator permission using below command. Please Get your sid from your  mdm.tblUser table

    and then pass that value to below command. 

    EXEC [mdm].[udpSecuritySetAdministrator] @UserName='DOMAIN\user_name', @SID = 'SID', @PromoteNonAdmin = 1
    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit 


    Srinivasarao

    • Marked as answer by Alex_404 Friday, March 4, 2016 10:32 AM
    Friday, March 4, 2016 10:13 AM

All replies

  • Hi Alex,

    You can get Administrator permission using below command. Please Get your sid from your  mdm.tblUser table

    and then pass that value to below command. 

    EXEC [mdm].[udpSecuritySetAdministrator] @UserName='DOMAIN\user_name', @SID = 'SID', @PromoteNonAdmin = 1
    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit 


    Srinivasarao

    • Marked as answer by Alex_404 Friday, March 4, 2016 10:32 AM
    Friday, March 4, 2016 10:13 AM
  • Hi Alex,

    You can get Administrator permission using below command. Please Get your sid from your  mdm.tblUser table

    and then pass that value to below command. 

    EXEC [mdm].[udpSecuritySetAdministrator] @UserName='DOMAIN\user_name', @SID = 'SID', @PromoteNonAdmin = 1
    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit 


    Srinivasarao

    Thanks, this would work in my case. I found another way to fix my problem though because this sp seems dangerous after I checked what it does.

    It will overwrite whoever has ID=1 in the mdm.tblUser table. So this does not grant an admin permission on all/any models but rather grants super user ID, which mean that the user previously having it would be lost.

    Friday, March 4, 2016 10:57 AM
  • It is for only one member and only time. Once you get admin access then you can give/revoke all other people permissions. This SP will use only when there is no way to access your models.

    Srinivasarao

    Friday, March 4, 2016 11:18 AM
  • It is for only one member and only time. Once you get admin access then you can give/revoke all other people permissions. This SP will use only when there is no way to access your models.

    Srinivasarao

    User previously having ID = 1 would still be lost (which is not me). I guess I could recreate that login and re-assign it as super user, but then I would lose my login (with it having now ID = 1) and would have to ask the owner of that account to re-add me.

    My way of fixing this was modifying [mdm].[viw_SYSTEM_SECURITY_USER_MODEL] view in MDS database to add my user ID as super user, in addition to user ID = 1. This view is apparently used in WebUI to determine admin rights, so I got my super user access without losing the current super user information. I still could not change the "deny" I had on the attribute but I could remove the second administrator's "deny" permissions as well as re-grant him "update" permissions on the model, which effectively restored control.



    • Edited by Alex_404 Friday, March 4, 2016 1:14 PM
    Friday, March 4, 2016 12:37 PM
  • Recommend to try out the new SQL 2016 MDS.

    We have removed implicit super user and mode admin (only update on model level) and added explicit permission.

    Friday, March 4, 2016 9:39 PM