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.
