如何为其他数据库中的登录名授予对SQL Server数据库邮件过程的访问权限？ [英] How to give access to SQL Server database mail procedures for a Login in a different database?

问题描述

我想知道如何在SQL Server中设置权限，以允许我的应用程序登录/角色能够使用 msdb.dbo.sp_send_dbmail 发送电子邮件。

I'd like to know how to set up permissions within SQL Server to allow my application Login/Role to be able to send email using msdb.dbo.sp_send_dbmail.

我有一个数据库MyDb，一个用户 MyUser 是角色 AppRole的成员。我有一个存储过程 myProc ，它调用 msdb.dbo.sp_send_dbmail 。如果我以 sa 登录时执行 myProc ，则一切正常，但如果我以<$登录时执行c $ c> MyUser 我收到一个错误：

I have a database MyDb, a user MyUser who is a member of role AppRole. I have a stored procedure myProc that calls msdb.dbo.sp_send_dbmail. If I execute myProc while logged in as sa it all works fine, but if I execute while logged in as MyUser I get an error:

Msg 229，级别14，状态5，过程sp_send_dbmail ，行1

对对象'sp_send_dbmail'，数据库'msdb'，模式'dbo'的执行权限被拒绝。

Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

我的数据库没有启用TRUSTWORTHY，因此，我相信我不能使用EXECUTE AS来模拟其他用户，例如使用EXECUTE AS OWNER创建myProc ...（ MSDN参考）

My database does not have TRUSTWORTHY ON, therefore I believe I can't use EXECUTE AS to impersonate a different user, e.g. create myProc with EXECUTE AS OWNER... (MSDN reference)

因此，我认为我需要让我的用户同时也是msdb中的用户，但是我可以在角色级别上做到这一点还是我需要让每个数据库用户都可以还用户在MSDB？

Therefore I think I need to make my user(s) also users within msdb, but can I do this at the Role level or do I need to make each of my database users also users in msdb?

我正在使用的数据库邮件配置文件设置为public，所以我认为这与配置文件权限无关。

The database mail profile that I'm using is set to public, so I don't think this is related to the profile permissions.

推荐答案

您可以使用EXECUTE AS并对过程进行签名，然后使用签名证书在 msdb 。请参阅调用过程，以及使用证书签署程序 。

You can use EXECUTE AS and sign your procedure and then use the signature certificate to grant EXECUTE permission in msdb. See Call a procedure in another database from an activated procedure, as well as Signing Procedures with Certificates.

这篇关于如何为其他数据库中的登录名授予对SQL Server数据库邮件过程的访问权限？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！