Lesson Learned #470: Resolving 'EXECUTE Permission Denied' Error on sp_send_dbmail in Azure SQL MI
Published Jan 08 2024 01:14 AM 2,418 Views

We worked on a service request that our customer encountering an error message "Executed as user: user1. The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed.", I would like to share with you how was the resolution for this specific error message. 

 

Understanding the Error

 

The error message explicitly points to a permission issue. The user (in this case, 'user1') does not have the necessary permission to execute the sp_send_dbmail stored procedure located in the msdb database. This procedure is essential for sending emails from Azure SQL Managed Instance, and lacking execute permissions will prevent the Database Mail feature from functioning correctly.

 

In this situation, we identified that the user1 was not part DatabaseMailUserRole role in the msdb database. Membership in this role is a prerequisite for using Database Mail. 

 

USE msdb;
ALTER ROLE DatabaseMailUserRole ADD MEMBER [user1];


Once the permission was granted the 'user1' was able to successfully send emails through Database Mail in Azure SQL Managed Instance.

 

 

Version history
Last update:
‎Jan 08 2024 01:14 AM
Updated by: