How to execute sp_send_dbmail while limiting permi

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

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] '[e-mail]', '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] '[e-mail]', '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

*/

Answer 2

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 3

One possible solution is to encapsulate the mailing as a stored procedure e.g. mail_error_as_MAILER (wich you will later call) and another stored procedure e.g.

ALTER PROCEDURE [dbo].[mail_error](@error_ID int)
SET NOCOUNT ON
declare @rc int
declare @object int
declare @src varchar(255)
declare @desc varchar(255)
declare @osql_cmd varchar(1000)
-- create shell object
exec @rc = sp_oacreate 'wscript.shell', @object out
if @rc0 
  begin
    exec sp_oageterrorinfo @object, @src out, @desc out
    return
  END

  DECLARE @user VARCHAR(50)
  DECLARE @password VARCHAR(50)
  DECLARE @database VARCHAR(50)
  DECLARE @server VARCHAR(50)
  DECLARE @sql varchar(200) 
 SET @user=MAILER,@password=XXXXXX,@database=XXXXXX,@server=XXXXX 
SET @sql= 'EXEC mail_ERROR_as_MAILER @error_ID=' + CAST(@error_id as varchar(10))
set @osql_cmd='osql -U'+@user+' -P'+@password+' -d'+@database+' -S"'+@server+'"  -Q"'
  +@sql+'"'

exec @rc= sp_oamethod @object, 'run', null, @osql_cmd
--print @rc
if @rc0 
  begin
    exec sp_oageterrorinfo @object, @src out, @desc out
    return
  end
-- destroy shell object
exec sp_oadestroy @object

But this requires: * hardcoding password (make sure users cannot view definition of stored procedure ...) * giving users access to sp_oacreate etc... (opens up other security problems e.g. DOS)

that way they can only use the mail the way you want to without giving them permission to mail other things.

Or safer, let users put mail in some kind of mail_queue(wich you can control what they can put in) and have an agent acount send these mails periodically

Or: give them permission to mail but take a big club with rusted spikes on abuse