Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.5k views
in Technique[技术] by (71.8m points)

sql server 2005 - How to execute sp_send_dbmail while limiting permissions

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!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...