Database Mail is a great new feature in SQL Server 2005 that utilizes Service Broker to asynchronously send emails from SQL Server. Disabled by default, the feature is easily enabled by using sp_configure and then creating a mail profile and account using the Database Mail Wizard in SQL Server Management Studio. While Database Mail offers the ability to queue mail for asynchronous delivery through the use of the msdb stored procedure sp_send_dbmail, when used by a stored procedure in another database it often generates an error similar to the following:

Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

There are a couple of different ways to work around this error. The first way would be to grant rights to the logins that need to call the stored procedure or code that executes the sp_send_dbmail stored procedure. They require access to the msdb database and then need to be added them to the DatabaseMailUserRole in msdb as documented in the Books Online Topic - Troubleshooting Database Mail: Permission denied on sp_send_dbmail. This role provides all of the permissions necessary to use Database Mail. However, this may not be an ideal solution in some environments.

The next common way to work around the error is to set TRUSTWORTHY ON for the database that has the stored procedure that calls sp_send_dbmail. This allows for Impersonation across databases to gain access to resources in other databases on the server. It is not a secure method of solving the problem since having TRUSTWORTHY ON allows for other side effects. If the database owner is a sysadmin, then TRUSTWORTHY ON provides full access to the server. This flag is also set OFF whenever a database is attached or restored to SQL Server. This is an easy to miss item that can become problematic in the worst possible times, like during disaster recovery.

While other solutions exist to solve this problem, the best solution involves using a certificate signed assembly to call sp_send_dbmail. This solution prevents the need to take additional steps to add users to msdb and the DatabaseMailUserRole, and it removes the risks associated with having TRUSTWORTHY ON.

To create a certificate signed assembly, first create the stored procedure.

-- Create a database for the Example
CREATE DATABASE TestDBMail
GO
-- Switch the database
USE [TestDBMail]
GO
-- Create a wrapper procedure 
CREATE PROCEDURE [dbo].[TestSendMail]
(
@To NVARCHAR(1000),  
@Subject NVARCHAR(100),  
@Body NVARCHAR(MAX)
)
WITH EXECUTE AS OWNER
AS
BEGIN
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = N'Database Mail Profile',
@recipients = @To, 
@subject = @Subject, 
@body = @Body    
END
GO

To reproduce the common problem encountered when using sp_send_dbmail in this manner attempt to execute the above procedure.

-- This should fail
EXECUTE [dbo].[TestSendMail] 'someemail@domain.com', 'test', 'body'

To create the solution, begin by creating a certificate in the database that the stored procedure was created in with a strong password.

-- Create a certificate to sign stored procedures with
CREATE CERTIFICATE [DBMailCertificate]
ENCRYPTION BY PASSWORD = '$tr0ngp@$$w0rd'
WITH SUBJECT = 'Certificate for signing TestSendMail Stored Procedure';
GO

Then create a backup the certificate to disk. At this point the backup should also be secured with other important backups as well.

-- Backup certificate so it can be create in master database
BACKUP CERTIFICATE [DBMailCertificate]
TO FILE = 'd:\Backup\DBMailCertificate.CER';
GO

Then create the certificate from the backup in the master database and create a login from the certificate.

-- Add Certificate to Master Database
USE [master]
GO
CREATE CERTIFICATE [DBMailCertificate]
FROM FILE = 'd:\Backup\DBMailCertificate.CER';
GO
-- Create a login from the certificate
CREATE LOGIN [DBMailLogin]
FROM CERTIFICATE [DBMailCertificate];
GO

In order for a certificate based login to access server scoped system tables, it must have the AUTHENTICATE SERVER right granted to it as documented in the Books Online Topic - Troubleshooting Metadata Visibility.

-- The Login must have Authenticate Sever to access server scoped system tables
-- per http://msdn.microsoft.com/en-us/library/ms190785.aspx
GRANT AUTHENTICATE SERVER TO [DBMailLogin]
GO

Then add a user in msdb for the certificate login.

-- Create a MSDB User for the Login
USE [msdb]
GO
CREATE USER [DBMailLogin] FROM LOGIN [DBMailLogin]
GO

Then add the DatabaseMailUserRole to the msdb user for the certificate login.

-- Add msdb login/user to the DatabaseMailUserRole
EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole', @membername = 'DBMailLogin';
GO

Finally sign the stored procedure in your database with the certificate.

USE [TestDBMail]
GO 
-- Sign the procedure with the certificate's private key
ADD SIGNATURE TO OBJECT::[TestSendMail]
BY CERTIFICATE [DBMailCertificate] 
WITH PASSWORD = '$tr0ngp@$$w0rd';
GO

Retesting the stored procedure will now succeed.

-- This will succeed
EXECUTE [dbo].[TestSendMail] 'someemail@domain.com', 'test', 'body'

Finally cleanup the objects created by this demonstration as a part of general good housekeeping when testing any kind of functionality based on demonstration code.

/*
USE [msdb]
GO
DROP USER [DBMailLogin]
GO
USE [master]
GO
DROP LOGIN [DBMailLogin]
DROP CERTIFICATE [DBMailCertificate]
DROP DATABASE [TestDBMail]
-- Delete the certificate backup from disk
*/

While this solution requires significantly more steps to create, it is the most secure method of solving this problem and can be used as a foundation for other solutions using Database Mail in SQL Server 2005 and 2008.