none
Get The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo', even when user has permissions RRS feed

  • Question

  • I get the error message:  The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.  This happens when I run a job, even though the user has the correct permission on sp_send_dbmail and is a user in msdb.

    If I run the procedure through SSMS it runs just fine and sends the mail.

    I have run out of things to look for, any ideas on what else it could be?

    Thanks in advance,

    Nancy

    Thursday, April 3, 2014 3:11 PM

Answers

  • To send Database mail, users must be a user in the msdb database and a member of the DatabaseMailUserRole database role in the msdb database. To add msdb users or groups to this role use SQL Server Management Studio or execute the following statement for the user or role that needs to send Database Mail.

    EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole'
        ,@membername = '<user or role name>';
    GO
    

    http://technet.microsoft.com/en-us/library/ms188719(v=sql.105).aspx


    Regards, RSingh

    Thursday, April 3, 2014 3:38 PM

All replies

  • To send Database mail, users must be a user in the msdb database and a member of the DatabaseMailUserRole database role in the msdb database. To add msdb users or groups to this role use SQL Server Management Studio or execute the following statement for the user or role that needs to send Database Mail.

    EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole'
        ,@membername = '<user or role name>';
    GO
    

    http://technet.microsoft.com/en-us/library/ms188719(v=sql.105).aspx


    Regards, RSingh

    Thursday, April 3, 2014 3:38 PM
  • When you run the procedure in SSMS, it is being executed under the context of your account.  When the procedure is executed via a job in SQL Agent, by default it will run under the context of the SQL Agent service account.  

    On the job step, I would recommend going to the advanced page then select a login for the run-as user.

    Best of luck.

    -D

    Tuesday, April 8, 2014 3:39 AM
  • Probably you need to look at the SQL Agent log-on account and fix any permission issues 

    Satheesh
    My Blog | How to ask questions in technical forum


    Tuesday, April 8, 2014 3:51 AM
  • If you read her question, she already has the user added to the DatabaseMailUserRole. I'm having the same issue, where if I run the contents of the failing job step in a query window, using the "run as user" I specified in the job step, everything runs fine. But if I run the job directly, I get the error messag:

    Msg 50000, Level 14, State 1, Procedure spxxxx, Line 293
    The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

    Thursday, May 29, 2014 8:36 PM