如何设置对MSSQL 2005数据库邮件的访问？

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.

我刚刚在MSSQL 2005中设置了数据库邮件。我已经能够从服务器上的管理员帐户和SQL Server代理作业发送电子邮件。但是,当我尝试通过只能访问我们的Web应用程序的数据库的登录信息发送电子邮件时,我无法发送电子邮件。

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

设置数据库邮件访问权限的最佳方法是什么?

I have 2 senerios where I want to send email.

我有2个senerios,我想发送电子邮件。

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)

通过SQL Server代理安排作业以运行执行某些过程的存储过程,然后发送电子邮件。 (这不是问题)

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)

从调用另一个可以发送电子邮件的存储过程的Web应用程序调用存储过程。 (这是我的权限问题)

Possible Solutions

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.

我发现授予登录权限的唯一方法是将登录映射到msdb数据库并授予public和DatabaseMailUserRole。这真的让我担心,因为我认为这可以提供对msdb数据库的大量访问。
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.

将要发送的电子邮件存储在表中,然后每隔一段时间让SQL Server代理作业查看该表以发送任何排队的电子邮件。这样,Web应用程序的数据库登录不会执行[msdb]。[dbo]。[sp_ send _dbmail]调用,因此不需要msdb数据库的任何权限。

Are there any other solutions?

还有其他解决方案吗?

2 个解决方案

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

您应该能够创建一个存储过程WITH EXECUTE AS OWNER选项,以便它在所有者而不是调用者的上下文中运行。

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

#2

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:

(我只使用“WITH ENCRYPTION”创建它)并将运行此过程的用户添加到msdb DatabaseMailUserRole角色:

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

Thanks

#1