How to execute sp_send_dbmail while limiting permissions

Question

Is there a way to provide access to users in my database to execute msdb.dbo.sp_send_dbmail without needing to add them to the MSDB database and the DatabaseMailUserRole?

I've tried this:

ALTER 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'myProfile',
            @recipients = @To, @subject = @Subject, @body = @Body
    END

But I get this error:

The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

Thanks!

Answer 1

Your approach is OK, but your wrapper proc must be in the msdb database. Then, you execute "EXEC msdb.dbo._TestSendMail"

This still leave the issue of permissions on dbo._TestSendMail in msdb. But public/EXECUTE will be enough: it only exposes the 3 parameters you need.

If in doubt, add WITH ENCRYPTION. This is good enough to stop anyone without sysadmin rights viewing the code

USE msdb
GO
CREATE PROCEDURE [dbo].[_TestSendMail]
(
  @To NVARCHAR(1000),
  @Subject NVARCHAR(100),
  @Body NVARCHAR(MAX)
)
-- not needec WITH EXECUTE AS OWNER
AS 
    BEGIN
        EXEC dbo.sp_send_dbmail @profile_name = N'myProfile',
            @recipients = @To, @subject = @Subject, @body = @Body
    END

Answer 2

You actually can do it with a certificate signed stored procedure and it doesn't have to be in msdb to do so:

CREATE DATABASE TestDBMail
GO

USE [TestDBMail]
GO

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

-- This should fail
EXECUTE [dbo].[TestSendMail] '[email protected]', 'test', 'body'

-- 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

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

-- 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

-- 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

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

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

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

-- This will succeed
EXECUTE [dbo].[TestSendMail] '[email protected]', 'test', 'body'

/*
-- Cleanup
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

*/