如何改写TRY/CATCH以捕获sp_send_dbmail权限拒绝错误？

问题原因 #

Msg 229这类权限错误属于编译阶段错误，而SQL Server的TRY/CATCH仅能捕获运行阶段触发的错误。当用户没有sp_send_dbmail的EXECUTE权限时，SQL Server在编译TRY块内的代码时就会直接抛出错误，根本不会进入TRY块执行，自然无法触发CATCH逻辑。

解决方案 #

方案一：使用动态SQL将错误转为运行时错误 #

动态SQL是在运行阶段才编译执行的，所以权限错误会被推到运行时，从而被TRY/CATCH捕获。推荐用sp_executesql传递参数，避免SQL注入风险：

BEGIN TRY
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = N'EXEC msdb.dbo.sp_send_dbmail 
        @Profile_Name = @p_Profile_Name
        , @Recipients = @p_Recipients
        , @Subject = @p_Subject
        , @Body = @p_Body
        , @File_Attachments = @p_File_Attachments'

    EXEC sp_executesql 
        @SQL,
        N'@p_Profile_Name NVARCHAR(128), @p_Recipients NVARCHAR(MAX), @p_Subject NVARCHAR(255), @p_Body NVARCHAR(MAX), @p_File_Attachments NVARCHAR(MAX)',
        @p_Profile_Name = 'SQL_Email',
        @p_Recipients = @CurrentEmail,
        @p_Subject = @Subject,
        @p_Body = @Body,
        @p_File_Attachments = @FilePath
END TRY
BEGIN CATCH
    -- 这里可以处理权限错误，比如记录日志、返回提示等
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
    RETURN
END CATCH

方案二：提前检查用户权限 #

在执行sp_send_dbmail前，先通过系统函数检查当前用户是否拥有EXECUTE权限，提前处理无权限的情况：

-- 检查是否有sp_send_dbmail的EXECUTE权限
DECLARE @HasPermission BIT = 0;
SELECT @HasPermission = 1
FROM sys.fn_my_permissions('msdb.dbo.sp_send_dbmail', 'OBJECT')
WHERE permission_name = 'EXECUTE';

IF @HasPermission = 1
BEGIN
    EXEC msdb.dbo.sp_send_dbmail 
        @Profile_Name = 'SQL_Email'
        , @Recipients = @CurrentEmail
        , @Subject = @Subject
        , @Body = @Body
        , @File_Attachments = @FilePath
END
ELSE
BEGIN
    -- 无权限时的处理逻辑
    RAISERROR('当前用户无发送邮件权限，请联系管理员', 16, 1);
    RETURN
END

两种方案对比 #

• 动态SQL方案：无需额外权限检查逻辑，能捕获所有运行时错误，但需要注意参数传递的安全性，避免注入。
• 权限预检查方案：逻辑更直观，提前拦截错误，但需要确保sys.fn_my_permissions的查询权限对当前用户开放。

内容的提问来源于stack exchange，提问作者TheMortiestMorty