调用sp_send_mail时出现Msg 22050和Msg 14661错误-CSDN博客

环境：SQL Server 9.0.4053
使用域帐号mydomain\hex登录，利用msdb.dbo.sp_send_dbmail发送邮件，在使用其中的@query参数后就出现错误。
USE msdb
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Myprofile',
    @recipients = 'MyprofileMail@mydomain.com',
    @query = 'select DriveLetter,MB_Free,Status from dbo.DrvSpace',
    @subject = 'MYSER55\SQL2005 Last Backup Report',
    @attach_query_result_as_file = 1 ;
GO
-------------------------------------------
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495
Query execution failed: Msg 15404, Level 16, State 19, Server FIHSER55\SQL2005, Line 1
Could not obtain information about Windows NT group/user 'mydomain\hex', error code 0x5.

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495
Query execution failed: Msg 208, Level 16, State 1, Server MYSER55\SQL2005, Line 1
Invalid object name 'dbo.DrvSpace'.

注：
[ @query = ] 'query' 表示要执行的查询。查询结果可以作为文件附加，或包含在电子邮件的正文中。查询的类型为 nvarchar(max)，并且可以包含任何有效的 Transact-SQL 语句。

分析：
在进行一般性发送邮件都正常，而用上@query查询功能并做为一个附件就会出错，错误信息显示不能得到域帐号的信息。

通用切换SQL Server帐号test登录运行：
USE msdb
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Myprofile',
    @recipients = 'MyprofileMail@mydomain.com',
    @query = 'select DriveLetter,MB_Free,Status from dbo.DrvSpace',
    @subject = 'MYSER55\SQL2005 Last Backup Report',
    @attach_query_result_as_file = 1 ;
GO
--------------------------------------------------------------------------------------
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495
Query execution failed: Msg 15406, Level 16, State 1, Server MYSER55\SQL2005, Line 1
Cannot execute as the server principal because the principal "test" does not exist, this type of principal cannot be impersonated, or you do not have permission.
是由于没有服务器权限的问题，为什么还要server principal？

查看SQL Server服务启动的帐号为:MYSER55\SQL1，它应该是具有SERVER系统的权限。
将MYSER55\SQL1帐号加入到msdb数据库中，并以MYSER55\SQL1做为登录帐号：
USE msdb
EXECUTE AS LOGIN = 'MYSER55\SQL1'
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Myprofile',
    @recipients = 'MyprofileMail@mydomain.com',
    @query = 'select DriveLetter,MB_Free,Status from dbo.DrvSpace',
    @subject = 'MYSER55\SQL2005 Last Backup Report',
    @attach_query_result_as_file = 1 ;
GO
---------------------------------------------------------------------
mail quene.

邮件发送成功！

如果使上述邮件发送成功，需要具有Windows 系统相关的权限.
验证Windows用户和 Windows 组的信息
EXEC sys.xp_logininfo @acctname = 'MYSER55\SQL1'
------------------------------------------------
Command(s) completed successfully.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9932141/viewspace-664801/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9932141/viewspace-664801/