SQL Server单用户msdb.dbo.sysmail_profile的SELECT授权不生效问题

核心原因 #

• 目标登录名未在msdb数据库中创建对应的映射用户，导致授权不生效
• 用户缺少msdb数据库的CONNECT权限，无法访问库内对象
• SQL Server msdb库的邮件相关系统对象默认仅授予DatabaseMailUserRole角色访问权限，单独对表赋权可能被更高优先级的权限规则覆盖

方案1（推荐，符合微软安全规范） #

无需直接对表单独赋权，使用msdb内置的数据库角色即可满足需求，权限控制更安全。

1. 若目标用户还未在msdb库中创建映射，先执行创建语句：

USE msdb;
GO
-- 替换[对应登录名]为用户的服务器登录名
CREATE USER [My User] FOR LOGIN [对应登录名];
GO

2. 将用户加入DatabaseMailUserRole角色，该角色默认拥有sysmail系列对象的查询权限：

-- SQL Server 2012及以上版本推荐用法
ALTER ROLE DatabaseMailUserRole ADD MEMBER [My User];
GO

-- 兼容旧版本用法
-- EXEC sp_addrolemember 'DatabaseMailUserRole', 'My User';
-- GO

执行完成后用户即可正常执行select * from msdb..sysmail_profile语句，无需给Public角色开放权限。

方案2（自定义赋权，不使用内置角色） #

如果你需要完全自定义权限规则，不使用内置角色，按以下步骤操作：

1. 在msdb库中创建目标用户的映射（同方案1步骤1）
2. 授予用户msdb库的CONNECT权限：

USE msdb;
GO
GRANT CONNECT ON DATABASE::msdb TO [My User];
GO

3. 授予sysmail_profile表的SELECT权限：

GRANT SELECT ON dbo.sysmail_profile TO [My User];
GO

隐式权限排查方法 #

如果执行上述操作后仍报错，可执行以下语句排查用户所属角色是否存在隐式DENY权限，注意：权限优先级中DENY始终高于GRANT，只要用户或所属任意角色有对应对象的DENY权限，授权就会失效：

USE msdb;
GO
SELECT 
    dp.permission_name,
    dp.state_desc,
    OBJECT_NAME(dp.major_id) AS object_name,
    USER_NAME(dp.grantee_principal_id) AS grantee
FROM sys.database_permissions dp
JOIN sys.database_principals gr ON dp.grantee_principal_id = gr.principal_id
WHERE 
    OBJECT_NAME(dp.major_id) = 'sysmail_profile'
    AND (
        gr.name = 'My User' 
        OR gr.name IN (
            SELECT role.name 
            FROM sys.database_role_members rm
            JOIN sys.database_principals role ON rm.role_principal_id = role.principal_id
            JOIN sys.database_principals u ON rm.member_principal_id = u.principal_id
            WHERE u.name = 'My User'
        )
    );

如果查询结果中出现state_desc为DENY的记录，需要先收回对应DENY权限，授权才可生效。

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