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

Source: Internet
Author: User

 

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.