如何在限制权限的同时执行sp_send_dbmail [英] How to execute sp_send_dbmail while limiting permissions

问题描述

是否可以提供一种方法来访问数据库中的用户以执行 msdb.dbo.sp_send_dbmail ，而无需将其添加到MSDB数据库和DatabaseMailUserRole中?

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?

我已经尝试过了:

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

但是我得到这个错误:

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

谢谢！

推荐答案

您的方法还可以，但是包装过程必须在msdb数据库中. 然后，执行"EXEC msdb.dbo._TestSendMail"

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

这仍然留下了msdb中dbo._TestSendMail的权限问题. 但是public/EXECUTE就足够了:仅公开您需要的3个参数.

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.

如有疑问，请添加WITH ENCRYPTION.这足以阻止任何没有系统管理员权限的人查看代码

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

这篇关于如何在限制权限的同时执行sp_send_dbmail的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！