如何在限制权限的同时执行 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屋!