How to set up access to MSSQL 2005 Database Mail?

Question

I've just setup Database mail within MSSQL 2005. I have been able to send email from an administrator account on the server and through a SQL Server Agent job. But, I unable to send email when I tried to send email via a login that just has access to a database for our web application.

What is the best way to set up access to Database Mail?

I have 2 senerios where I want to send email.

1. Schedule a job through SQL Server Agent to run a Stored Procedure that does some process and then sends an email. (this hasn't been a problem)
2. Have a stored procedure invoked from a web application that calls another stored procedure which could send an email. (this is were my permissions issue is)

Possible Solutions

1. The only way that I have found to grant permission to a login is to map the login to the msdb database and grant the public and DatabaseMailUserRole. This really concerns me as I would assume that this gives to much access to the msdb database.

2. Store the email I want to send in a table and then have a SQL Server Agent job look at that table every so often to send any queued emails. This way the database login for the web application does not execute the [msdb].[dbo].[sp_ send _dbmail] call and thus does not need any permission to the msdb database.

Are there any other solutions?

Answer 1

You should be able to create a stored procedure WITH EXECUTE AS OWNER option so it runs in the context of the owner rather than the caller.

CREATE PROCEDURE [dbo].[sp_SendMail]
(
    @To nvarchar(1000),
    @Subject nvarchar(100),
    @Body nvarchar(max)
)
WITH EXECUTE AS OWNER
AS
BEGIN
    exec msdb.dbo.sp_send_dbmail @profile_name = 'MailProfile', 
       @recipients = @To, 
       @subject = @Subject, 
       @body = @Body
END

Answer 2

Hello,

I'm trying to do the same but executing

GRANT EXECUTE ON dbo.sp_SendMail TO User1

is not fixing this issue. Only thing which is making this procedure to work is not adding

EXECUTE AS OWNER

(I'm creating it with "WITH ENCRYPTION" only) and adding user which will be running this procedure to msdb DatabaseMailUserRole role:

USE msdb
GO
EXEC sp_adduser @loginame='USERNAME', @grpname='DatabaseMailUserRole'
GO

Thanks