SQL Database Mail Permission Issue

I have user that is not a part of any server role (except the default public). I got the issue when the user was sending emails using msdb.dbo.sp_send_dbmail.

For example, using:

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'MailProfile'

, @recipients = '[email protected]'

, @subject = 'Automated Test Results (Successful)'

, @body = 'The stored procedure finished successfully.'

I am getting the following 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'.

I found that  also the following Database Mail SPs and views will be bounced.

Note: If Users who are not the member of DatabaseMailUserRole cannot send emails except sysadmin. 

-- SPs--------

sp_helprolemember

sysmail_help_status_sp

sysmail_delete_mailitems_sp

-- views-------

sysmail_allitems

sysmail_sentitems

sysmail_event_log

sysmail_unsentitems

sysmail_faileditems

sysmail_mailattachments

Now the user is allowed to run those above operations, you can add to DatabaseMailUserRole with SQL Server Management Studio as follows.

Expand the Security then seelct "Login" then right-click on the user.

 
To send Database Mail, users must be a member of the DatabaseMailUserRole. Members of the sysadmin fixed server role and msdb db_owner role are automatically members of the DatabaseMailUserRole role. To list all other members of the DatabaseMailUserRole execute the following statement:

 

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';

 
Or to add the DatabaseMailUserRole role, use the following statement:

USE msdb

EXECsp_addrolemember@rolename='DatabaseMailUserRole', @membername=NewUser

Now you can see.

 
Now the new user is showing the role member.