blocks|key|962159|text|找到了对我有用的好的，简单的修复，这里|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|962160|如果您的SQL应用程序无法使用数据库邮件发送电子邮件(我假设您已经设置了DBMail帐户和配置文件设置)，那么有两件事需要设置：|962161|>+MANAGEMENT+>+DATABASE+>右击并选择配置…>选择“管理配置文件安全性”>“SQL管理”|ordered-list-item|962162|检查公共选项|962163|单击默认配置文件并将其设置为“是”。|962164|STUDIO+>数据库>系统数据库>右键单击MSDB并选择新查询>然后输入>授予sp_send_dbmail上的公共执行，然后单击OK|962165|entityMap|0|LINK|mutability|MUTABLE|url|http://www.urtech.ca/2013/05/solved-cannot-send-email-from-sql-database-mail-even-though-the-account-is-setup/^0|H|2|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]]|C|$]]|$1|D|3|E|5|6|7|10|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|11|8|@]|9|@]|C|$]]|$1|I|3|J|5|H|7|12|8|@]|9|@]|C|$]]|$1|K|3|L|5|H|7|13|8|@]|9|@]|C|$]]|$1|M|3|N|5|H|7|14|8|@]|9|@]|C|$]]|$1|O|3|-4|5|6|7|15|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]]]

Found nice and easy fix that worked for me <a href="http://www.urtech.ca/2013/05/solved-cannot-send-email-from-sql-database-mail-even-though-the-account-is-setup/" rel="noreferrer">here</a>:

If your SQL applications can’t send email using database mail (I assume you already have DBMail Account and Profile setup), there are two things to set:

<ol>
<li>SQL MANAGEMENT STUDIO > MANAGEMENT > DATABASE MAIL > right click and
select CONFIGURE… > select MANAGE PROFILE SECURITY > SQL MANAGEMENT </li>
<li>put a check on PUBLIC option</li>
<li>click on DEFAULT PROFILE and set it to YES</li>
<li>STUDIO > DATABASES > SYSTEM DATABASES > right click on MSDB and
select NEW QUERY > then enter > grant execute on sp_send_dbmail to
public and click OK</li>
</ol>

blocks|key|1936245|text|要发送数据库邮件，用户必须是msdb数据库中的用户和msdb数据库中DatabaseMailUserRole数据库角色的成员。若要向此角色添加msdb用户或组，请使用或为需要发送数据库邮件的用户或角色执行以下语句：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1936246|EXEC+msdb.dbo.sp_addrolemember+@rolename+=+'DatabaseMailUserRole'
++++,@membername+=+'<user+or+role+name>';
GO|code-block|syntax|javascript|1936247|entityMap^0|Y|K|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

To send Database mail, users must be a user in the msdb database and a member of the <code>DatabaseMailUserRole</code> database role in the msdb database. To add msdb users or groups to this role use SQL Server Management Studio or execute the following statement for the user or role that needs to send Database Mail: 

<pre><code>EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole'
 ,@membername = '&lt;user or role name&gt;';
GO
</code></pre>

blocks|key|1459746|text|授予执行存储过程的用户对sp_send_dbmail的执行权限，或将它们添加到角色msdb.DatabaseMailUser中。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1459747|entityMap^0|C|E|15|L|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|M|8|@]|D|@]|E|$]]]|G|$]]

Grant execute permission on <code>sp_send_dbmail</code> to the user executing the stored procedure, or add them to the role <code>msdb.DatabaseMailUser</code> .

blocks|key|1459818|text|好吧，只是想补充一下这个话题，因为这是非常好的信息，但仍然没有完全解决我的问题。如果I在SSMS中运行查询，则一旦获得在msdb中执行sp_send_dbmail过程的权限，它就能工作。但是，当作业以我的用户身份运行时，它仍然会失败。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|1459819|阅读大量资料可以得出这样的结论:您需要确保数据库中的所有者的sid与主DB中的所有者sid匹配：|1459820|--To+get+owner+SID+recorded+in+the+master+database+for+the+current+database
SELECT+owner_sid+FROM+sys.databases+WHERE+database_id=DB_ID()
--To+get+the+owner+SID+recorded+for+the+current+database+owner
SELECT+sid+FROM+sys.database_principals+WHERE+name=N'dbo'|code-block|syntax|javascript|1459821|尽管我已经给出了msdb的访问权限并在sp_send_dbmail上执行权限，但它仍然存在与数据库不可信任和所有者小岛屿发展中国家不匹配的问题。因此，我不得不对运行在其中的数据库进行信任，并修复所有权问题：|1459822|ALTER+DATABASE+my_db+SET+TRUSTWORTHY+ON;

ALTER+AUTHORIZATION+ON+Database::my_db+TO+[domain\user];|1459823|为了最终找到这个更有启发性的笔迹，我不得不经历了大量的反复探索。|1459824|entityMap|0|LINK|mutability|MUTABLE|url|http://blog.strictly-software.com/2009/09/database-owner-sid-recorded-in-master.html^0|16|1|0|0|0|0|0|E|2|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|13|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|14|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|15|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|16|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|17|8|@]|D|@$9|18|A|19|1|1A]]|E|$]]|$1|S|3|-4|5|6|7|1B|8|@]|D|@]|E|$]]]|T|$U|$5|V|W|X|E|$Y|Z]]]]

Ok, just to add to this topic since this was really good information, but still didn't completely solve my problem. If I ran the query in SSMS, it worked once I was granted permission to execute the sp_send_dbmail procedure in msdb. However, when a job was running as my user, it would still fail.

Read through a lot of stuff to get to the conclusion that you need to make sure the sid for the owner in your DB matches the owner sid in the master DB:

<pre><code>--To get owner SID recorded in the master database for the current database
SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()
--To get the owner SID recorded for the current database owner
SELECT sid FROM sys.database_principals WHERE name=N'dbo'

</code></pre>

Even though I had given access to the msdb and execute rights on the sp_send_dbmail, it was still having issues related to the database being untrustworthy and that the owner sids didn't match. Consequently, I had to the Trustworthy on for the database I was running in and fix the ownership issue:

<pre><code>ALTER DATABASE my_db SET TRUSTWORTHY ON;

ALTER AUTHORIZATION ON Database::my_db TO [domain\user];
</code></pre>

I had to go through a lot of ferreting around to finally find this <a href="http://blog.strictly-software.com/2009/09/database-owner-sid-recorded-in-master.html" rel="nofollow noreferrer">write-up</a> which is much more enlightening.

blocks|key|962193|text|我意外地将SQL代理作业的"Run“选项从"User”更改为“(未指定)”。这使我的工作开始工作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|962194|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I accidentally changed the "Run As" option for the SQL Agent Job - Step from "[User]" to "(Not Specified)". That caused my Job to start working.

blocks|key|1459864|text|我找到了这里适合我的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1459865|您需要将用户(例如mailuser)添加到msdb，并授予此用户运行dbmail的权限：|1459866|USE+msdb;
CREATE+USER+mailuser+FOR+LOGIN+mailuser;
EXEC+sp_addrolemember+'DatabaseMailUserRole',+'mailuser'|code-block|syntax|javascript|1459867|entityMap|0|LINK|mutability|MUTABLE|url|https://www.sqlservercentral.com/forums/topic/how-do-i-grant-permission-to-msdb-dbo-sp_send_dbmail^0|4|2|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

I found <a href="https://www.sqlservercentral.com/forums/topic/how-do-i-grant-permission-to-msdb-dbo-sp_send_dbmail" rel="nofollow noreferrer">here</a> the right solution for me.
You need to add your user (let's say mailuser) to msdb and give this user rights to run dbmail:
<pre><code>USE msdb;
CREATE USER mailuser FOR LOGIN mailuser;
EXEC sp_addrolemember 'DatabaseMailUserRole', 'mailuser'
</code></pre>

I am getting this error:

<blockquote>
 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'.
</blockquote>

The relevant part of the code:

<pre><code>/****** Object: StoredProcedure [dbo].[dbo.STATUSCHANGE_EMAILALERT] ******/

EXEC msdb.dbo.sp_send_dbmail
 @recipients = 'Test@gmail.com', -- Group Email
 @subject = 'Employee Status Update',
 @profile_name ='Test@gmail.com', -- Setup the profile name group
 @body = @body,
 @body_format = 'HTML';
</code></pre>

Failure to execute msdb.dbo.sp_send_dbmail

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云AI代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

功能1上新10个字符

功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符。

功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符。

功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符

功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符

功能4上新

文章&问答评论现已支持表情

全新交互，全新视觉，新增快捷键、悬浮工具栏、高亮块等功能并同时优化现有功能，全面提升创作效率和体验

社区富文本编辑器全新改版！诚邀体验～ 

精选全网热门MCP server，让你的AI更好用 🚀

💥开发者 MCP广场重磅上线！

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我得到了这个错误：Msg 229，14级，状态5，过程sp_send_dbmail，第1行对象'sp_send_dbmail‘、数据库'msdb’、架构'dbo‘上的EXECUTE权限被拒绝。守则的有关部分：/****** Object:  StoredProcedure [dbo].[dbo.STATUSCHANGE_EMAILALERT] ******/EXEC msdb.dbo.sp_sen

问执行msdb.dbo.sp_send_dbmail失败
EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问执行msdb.dbo.sp_send_dbmail失败EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问执行msdb.dbo.sp_send_dbmail失败
EN