locked
The EXECUTE permission was denied on the object 'sp_send_dbmail' - tried everything, still happening RRS feed

  • Question

  • I've got a SQL Agent job that's supposed to run a stored proc and e-mail the results.  I keep getting the following error:  

    Executed as user: <user>. The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229).  The step failed.

    Naturally, the code for the job step works just fine in SSMS.  

    I've read a bunch of forum posts on this issue and tried the "solutions" they recommend.  So far, I have:

    • Specified the user name in "Run as".
    • Added the user to the MSDB db.
    • Added the user to "db_owner" in MSDB.
    • Added the user to "sysadmin" on the server.
    • Given the user permissions to the mail profile used by Database Mail.  

    None of these "solutions" has worked.  I'm stuck.  Anybody got any ideas?  The server is running SQL 2016 SP1...is this maybe a bug?  

    Friday, December 1, 2017 10:00 PM

All replies

  • Hi Ceemack530,

    Try this method:

    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

    Thanks

    Monday, December 4, 2017 4:46 AM
  • Grant EXEC on sp_send_dbmail to user


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx


    • Edited by AV111 Monday, December 4, 2017 5:13 AM database/mailUserRole should assigned
    Monday, December 4, 2017 4:56 AM
  • See

    https://technet.microsoft.com/en-us/library/ms188719%28v=sql.105%29.aspx?f=255


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, December 4, 2017 4:59 AM
  • I added the SQL login to the DatabaseMailUserRole, re-ran the job and got the same results.  
    Monday, December 4, 2017 5:47 PM
  • Did that.   No results.  

    Stored_Procedure Granteee_Principal_id         permission_name
    sp_send_dbmail         DatabaseMailUserRole EXECUTE
    sp_send_dbmail         cad_updates                 EXECUTE

    Monday, December 4, 2017 5:48 PM
  • A SQL Agent job runs as the SQL Agent service account, unless you created a Proxy account.  The SQL Agent service account needs to be added to the msdb DatabaseMailUserRole.

    After adding the right, restart SQL Agent.

    Monday, December 4, 2017 7:07 PM
  • Already done.  No effect.  
    Monday, December 4, 2017 7:31 PM
  • Do you have a public profile? You may need to mark a profile as public for users to be able to use it.

    Select Manage Profile Security and make a profile public.

    Monday, December 4, 2017 8:25 PM
  • Nice idea, but no joy.  
    Tuesday, December 5, 2017 5:29 PM