Failure to execute msdb.dbo.sp_send_dbmail

Question

I am getting this error:

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

The relevant part of the code:

/****** Object:  StoredProcedure [dbo].[dbo.STATUSCHANGE_EMAILALERT] ******/

EXEC msdb.dbo.sp_send_dbmail
  @recipients = '[email protected]', -- Group Email
  @subject = 'Employee Status Update',
  @profile_name ='[email protected]', -- Setup the profile name group
  @body = @body,
  @body_format = 'HTML';

Answer 1

Found nice and easy fix that worked for me here:

If your SQL applications can’t send email using database mail (I assume you already have DBMail Account and Profile setup), there are two things to set:

1. SQL MANAGEMENT STUDIO > MANAGEMENT > DATABASE MAIL > right click and select CONFIGURE… > select MANAGE PROFILE SECURITY > SQL MANAGEMENT
2. put a check on PUBLIC option
3. click on DEFAULT PROFILE and set it to YES
4. STUDIO > DATABASES > SYSTEM DATABASES > right click on MSDB and select NEW QUERY > then enter > grant execute on sp_send_dbmail to public and click OK

Answer 2

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

Answer 3

Grant execute permission on sp_send_dbmail to the user executing the stored procedure, or add them to the role msdb.DatabaseMailUser .