When SQL Server calls database mail to send an email, the following error occurs: MSG 229, Level 14, state 5, procedure sp_send_dbmail, line 1 Execute

 

When a new account uses table Tigger to call database mail to send an email, the following error occurs: 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 the permission to send database emails in the MSDB database. You need to join the MSDB database user and assign the permission by adding the sp_addrolemember role.

Assume that the SQL Server login account is named "dba"

Use MSDB

Go

Create user dBA for login DBA

Go

Exec DBO. sp_addrolemember @ rolename = 'databasemailuserrole ',

@ Membername = 'dba'

Go

 

At this time, the database email may still be sent again with 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 the mail sending permission in MSDB, this is not enough. He also needs the permission to use profile: "dba_profile.

 

Use MSDB

Go

Exec sysmail_add_principalprofile_sp @ principal_name = 'dba'

, @ Profile_name = 'dba _ profile'

, @ Is_default = 1

 

In this way, you can use the new account to call database mail to send emails.