locked
Email notification issue RRS feed

  • Question

  • Hello,

    I finally found the cause of an email notification issue I had marked in another thread, but I don't find the resolution.

    Here's for short: I have a business rule with a notification configured which it didn't seem to work.
    I tried again the standard steps on another MDS application and I succeeded. One remarks although: only the status change of a member from "valid" to "invalid" gives an email notification! It is not so obvious at the first time (and frankly I would like to have somewhere in time later more flexibility on that...).

    So I came back to my original application and I looked the SLQ Logs:

    Date,Source,Severity,Message
    07/30/2010 01:00:53,spid26s,Unknown,The activated proc '[mdm].[udpNotificationQueueActivate]' running on queue 'MasterDataServices2.mdm.microsoft/mdm/queue/notification' output the following:  'The EXECUTE permission was denied on the object 'sp_send_dbmail'<c/> database 'msdb'<c/> schema 'dbo'.'

    Then I realized my situation: I had a blocking issue one time on that application - it arrived in an unstable status (like DB errors on standard MDS Explorer operations). But no useful DB backup available (it is a DEV environment). So at that time I decided to restore a similar MDS database from elsewhere. All was done and the MDS application based on restore works very well. Except for this email issue. Apparently the mds_email_user ID is not recognized (which is logical, it arrived from anoter database & server).

    Now, the question is: have you a solution on that? I was thinking on changing the DB email user for MDS (in MDS Configuration Manager, Database settings, there is a setting called "Database mail account"=mds_email_user), but the setting is disabled and cannot be changed.


    Marius
    Friday, July 30, 2010 7:30 AM

Answers

  • I think the problem is you are missing the mds_email_user in your msdb database.  This user gets created when you use MDS Configuration Manager to create a database.  Since you restored a database from elsewhere and presumably didn't create one via Config Mgr, you probably don't have this user.  It needs to exist in the msdb database and to have the DatabaseMailUserRole assisgned.

    Take a look at this link that Brian posted earlier:  http://msdn.microsoft.com/en-us/library/ff486994.aspx

    I think the easiest way to correct this is to use Config Manager to create another MDS database with a different name which you can then just delete via SSMS afterwards.  You're probably missing the mds_clr_user as well.

     
    Tuesday, August 3, 2010 5:50 PM
  • Hi Denise,

    Sorry for the late post. Actually I do have already 2 more databases created on the same server instance.

    I think my issue is related to the artificial operation I made - I restored an external database on this server instance.
    I will try soon to recreate the database on the same server and retest the notification.

    Thanks,


    Marius
    Tuesday, August 17, 2010 9:10 AM

All replies

  • The error message states that

    The EXECUTE permission was denied on the object 'sp_send_dbmail'<c/> database 'msdb'

    The user trying to send execute the dbmail, does not have permission to do that.

     


    Pradeep Adiga
    My blog: http://www.sqldbadiaries.com
    Friday, July 30, 2010 11:38 AM
  • Hi Marius,

    Run the following sproc to resync DB logins on a restored DB.

    EXEC sp_change_users_login 'Update_One', 'mds_email_user', 'mds_email_user';

    You can also use the ALTER USER command.

    Thanks,
    Brian Barnett- MSFT
    --------------------------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.

    Friday, July 30, 2010 12:15 PM
  • Marius,

    And you are correct that email notifications are only sent for validation issues.  Sorry for the confusion.  For future feature consideration what other types of email notifications were you envisioning?

    Thanks,
    Brian Barnett- MSFT

    Friday, July 30, 2010 12:32 PM
  • Brian,

    I applied your idea as this:

    USE MasterDataServices; -- my MDS DB
    EXEC sp_change_users_login 'Update_One', 'mds_email_user', 'mds_email_login'; -- your form is incorrect

    Still, I have the same error - Access Denied.


    Marius
    Friday, July 30, 2010 3:30 PM
  • Brian,

    In a short list, that would be:

    - send notifications simply on "Attribute has changed" (tracking notifications), without necessarily execute a business rule action (that is, having "Send email" as action would be a pretty good solution)
    - sent notifications on some of the system status change, like model version status etc.

    All in all, we would like to be capable to customize a more advanced workflow (based on statuses, notifications, access rights) directly in MDS.

    Btw, haven't you open a Vote Feature poll?... As it's made for Azure, Silverlight etc. It would be practical.


    Marius
    Friday, July 30, 2010 3:39 PM
  • Marius,

    Run to see if there are any orphaned users.
        EXEC sp_change_users_login 'Report';

    If so try auto fix (assuming mds_email_user is).
        EXEC sp_change_users_login 'Auto_Fix', 'mds_email_user'

    If the mds email user/login looks ok then I would next check the permissions.  The permissions should match what is listed in BOL here - http://msdn.microsoft.com/en-us/library/ff486994.aspx and http://msdn.microsoft.com/en-us/library/ff487057.aspx.

    Thanks,
    Brian Barnett - MSFT

    Friday, July 30, 2010 8:40 PM
  • Thanks Brian.

    EXEC sp_change_users_login 'Report': answer - 0;

    EXEC sp_change_users_login 'Auto_Fix', 'mds_email_user': answer - 0 accounts orphaned.

    Permissions checked - all are correct.

    Still the same issue.


    Marius
    Sunday, August 1, 2010 12:56 PM
  • I think the problem is you are missing the mds_email_user in your msdb database.  This user gets created when you use MDS Configuration Manager to create a database.  Since you restored a database from elsewhere and presumably didn't create one via Config Mgr, you probably don't have this user.  It needs to exist in the msdb database and to have the DatabaseMailUserRole assisgned.

    Take a look at this link that Brian posted earlier:  http://msdn.microsoft.com/en-us/library/ff486994.aspx

    I think the easiest way to correct this is to use Config Manager to create another MDS database with a different name which you can then just delete via SSMS afterwards.  You're probably missing the mds_clr_user as well.

     
    Tuesday, August 3, 2010 5:50 PM
  • Hi Denise,

    Sorry for the late post. Actually I do have already 2 more databases created on the same server instance.

    I think my issue is related to the artificial operation I made - I restored an external database on this server instance.
    I will try soon to recreate the database on the same server and retest the notification.

    Thanks,


    Marius
    Tuesday, August 17, 2010 9:10 AM