Users of Master Data Services: If you have recently restored your MDS database, and MDS is no longer working as it should, this article should help get you back up and running.

Run each of these scripts on your MDS database. In these examples, our database is called "MDS_Sample."

1. Change the database owner

USE MDS_Sample
go
sp_changedbowner 'mds_dlp_login'
go

 

2. Enable service broker
ALTER DATABASE MDS_Sample SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE MDS_Sample SET ENABLE_BROKER
ALTER DATABASE MDS_Sample SET MULTI_USER

 

3. Set trustworthy property on
ALTER DATABASE MDS_Sample SET TRUSTWORTHY ON;

 

4. Start conversations
--Script to start the member security message queue
DECLARE @memberHandle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @memberHandle
      FROM SERVICE [microsoft/mdm/service/system]
      TO SERVICE N'microsoft/mdm/service/securitymember'
      ON CONTRACT [microsoft/mdm/contract/securitymember]
      WITH ENCRYPTION=OFF; --is by default                 
 
--Script to Start the member security timer queue
DECLARE @memberSecHandle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @memberSecHandle
      FROM SERVICE [microsoft/mdm/service/securitymembertimer]
      TO SERVICE N'microsoft/mdm/service/system'
      --WITH RELATED_CONVERSATION_GROUP=0x1;
      WITH ENCRYPTION=OFF --is by default            
BEGIN CONVERSATION TIMER (@memberSecHandle) TIMEOUT = 30;
GO
--Script to Start the StagingBatch Queue
DECLARE @stagingHandle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @stagingHandle
      FROM SERVICE [microsoft/mdm/service/stagingbatch]
      TO SERVICE N'microsoft/mdm/service/system'
      WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER (@stagingHandle) TIMEOUT = 30;
GO
--Script to Start the Notification Queue
DECLARE @notifyHandle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @notifyHandle
      FROM SERVICE [microsoft/mdm/service/notification]
      TO SERVICE N'microsoft/mdm/service/system'
      WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER (@notifyHandle) TIMEOUT = 30;
GO

5. Enable CLR

--Switch on CLR
EXEC sp_configure 'clr_enable', 1;
RECONFIGURE WITH OVERRIDE;
GO

You might also want to check to ensure that all of these users have the permission they are supposed to have: http://msdn.microsoft.com/en-us/library/ff486994.aspx

If you run into these issues or others and want to contribute to this article, please do!