locked
MDS Email Notification fails to send, "Execute denied on sp_send_dbmail". RRS feed

  • Question

  • Hello everyone,

    I'm unable to get Email notification working in MDS. It was previously working, but hasn't since the database was restored from a different SQL Sever instance. I searched around online, this thread was helpful, but didn't result in a fix.

    The actual error message is:

    The activated proc '[mdm].[udpNotificationQueueActivate]' running on queue 'MDS.mdm.microsoft/mdm/queue/notification' output the following:  
    'The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.'


    So far I have:

    • Confirmed that the mds_email_user exists in msdb and MDS database and has the appropriate permissions
    • The mds_email_login is mapped to msdb as mds_email_user and MDS as mds_email_user
    • Checked for orphaned users, the DBA's here had already fixed that after restoring the DB.
    • Checked that database mail is functioning. The profile and account send test messages fine. 
    • Verified that the MDS database is set up to use the correct mail profile in the tblSystemSetting table
    • Created a second MDS database with the configuration tool. This was to see if the restore might be the cause of the problem. Notification works for this new database using the same mail profile as the restored database.

    Since notification works in the freshly created instance, but not the one that was restored, I think the issue is somewhere in the restored MDS database, but I'm not sure what else to look at.

    Any help would be greatly appreciated, I need to get this fixed since our users are expecting the notifications.

    Thanks,

    Isaac

     

    Friday, November 11, 2011 4:12 PM

Answers

  • Hi Ialtis,

    Try running the following script against your restored MDS database:

    ALTER DATABASE MDS SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    --Restored databases will have Service Broker enabled by default, this must be enabled for notifications

    ALTER DATABASE MDS SET ENABLE_BROKER

    -- After a restore of an MDS database, set the owner to the original owner

    ALTER AUTHORIZATION ON DATABASE::MDS TO [mds_dlp_login];

    -- Set trustworthy on for restored DBs

    ALTER DATABASE MDS SET TRUSTWORTHY ON;

    ALTER DATABASE MDS SET MULTI_USER

     

    Hope this helps.

    Pam Matthews

    www.profisee.com

     

     

    • Marked as answer by ialtis Friday, November 11, 2011 9:55 PM
    Friday, November 11, 2011 8:43 PM

All replies

  • Hi Ialtis,

    Try running the following script against your restored MDS database:

    ALTER DATABASE MDS SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    --Restored databases will have Service Broker enabled by default, this must be enabled for notifications

    ALTER DATABASE MDS SET ENABLE_BROKER

    -- After a restore of an MDS database, set the owner to the original owner

    ALTER AUTHORIZATION ON DATABASE::MDS TO [mds_dlp_login];

    -- Set trustworthy on for restored DBs

    ALTER DATABASE MDS SET TRUSTWORTHY ON;

    ALTER DATABASE MDS SET MULTI_USER

     

    Hope this helps.

    Pam Matthews

    www.profisee.com

     

     

    • Marked as answer by ialtis Friday, November 11, 2011 9:55 PM
    Friday, November 11, 2011 8:43 PM
  • 1.When you restore mdm db thse type of issue are quite seen alot . Try refreshing the dbowner once, maybe by changing the dbowner to any intermediate name and reverting back to actual.

    2.Verify the permission is according to MDM requirement http://msdn.microsoft.com/en-us/library/ff486994.aspx and you have all the user and their right proper.Ex- mdm_email_user have insert ,update and select permission for securable -dbo.

     

     

     

     

    Friday, November 11, 2011 9:36 PM
  • Thanks Pam,

    I had fixed the database owner previously, so it seems like the part I was missing was the AUTHORIZATION property.

    Isaac

     

     

    Friday, November 11, 2011 9:54 PM
  •  

    Can anyone shed a light on email notifications?

    Where does the mdm.microsoft/mdm/queue/notification get populated? I can see how [mdm].[udpNotificationQueueActivate] is called, but where is it populated?

    Thanks

    Monday, November 21, 2011 1:15 AM
  • I have the same Issue after the MDS database is restored.

    I am unable to get the notifications for the business rules. When  I checked the event log has the below error

    The activated proc '[mdm].[udpNotificationQueueActivate]' running on queue 'MDS.mdm.microsoft/mdm/queue/notification' output the following:  
    'The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.'

    Is there any resolution for this issue?


    • Edited by Arshad Jan Monday, September 2, 2013 5:24 PM
    Monday, September 2, 2013 2:19 PM