SQL Server calls Database Mail to send mail error: Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1 EXECUTE


Msg 229, Level 14, State 5, Procedure SP_send_dbmail, Line 1, EXECUTE Permission denied on Object ‘sp_send_dbmail’, Database ‘MSDB ‘, Schema’ dbO ‘.
This is because the current SQL Server login account (login) does not have permission to send database mail in the MSDB database, so you need to join the MSDB database user and give permission by joining the SP_addrolemEMBER role.
Assume that the SQL Server login account name is “DBA”
use msdb
The go
create user dba for login dba
Exec dbo.sp_addrolemember @rolename = ‘DatabaseMailUserRole’,
@ membername = ‘dbas’
At this point, if you send the database mail again, you may still have an error:
Msg 14607, Level 16, State 1, Procedure sp_send_dbmail, Line 119
profile name is not valid
Although the database user “DBA” already has permission to send messages in MSDB, this is not enough; he also needs permission to use profile: “dba_profile”.
use msdb
Exec sysmail_add_principalprofile_sp@principal_name = ‘DBA’
, @ profile_name = ‘dba_profile’
@ is_default = 1
This allows you to call Database Mail to send messages using your new account.

Read More: