0

I was exploring & experimenting Master Data Services of SQL Server 2012. I have discovered that read only functionality is available for the attribute (column). I have marked a column as read only & it worked; however, I have lost all admin permissions. I can still can explore the model but nothing else.

How to get back admin permissions on MDS when model is not listed under both permissions and system administration views. I still have admin permissions on database itself.

Chris Barlow
  • 3,274
  • 4
  • 31
  • 52
BI Dude
  • 1,842
  • 5
  • 37
  • 67

1 Answers1

1

Sometimes models "disappear" from permissions and system views when you set permissions for Code attribute of a model to read_only.

If you still have access to the underlying database you can update member permissions directly on database level.

You should update the privlege ID in tables tblSecurityRoleAccessMember and/or tblSecurityRoleAccess for the members in question.

If you need the changes to take effect immediately you have to perform the following SP 

USE [database];
GO
DECLARE @Model_ID INT;
SELECT @Model_ID = ID FROM mdm.tblModel WHERE [Name] = N'Model_Name';
EXEC [mdm].[udpSecurityMemberProcessRebuildModel]
@Model_ID=@Model_ID, @ProcessNow=1;
GO

Details about the set up of security tables are described in the following article.

http://www.rad.pasfu.com/index.php?/archives/145-Deep-Dive-into-Security-Schema-of-Master-Data-Services-Database.html

Narti
  • 181
  • 7