SQL Server查询CERTIFICATE为安全对象的持有权限用户失败问题

问题根因 #

• 过滤条件不匹配：你当前WHERE子句固定写死了dp.principal_id = 40，但目标用户的principal_id为6，自然无法返回目标用户的权限记录。
• major_id语义理解错误：sys.database_permissions表中major_id的含义完全由class_desc字段决定：当class_desc = 'CERTIFICATE'时，major_id对应[master].sys.certificates表的certificate_id，而非database_principals的principal_id，所以你从database_principals查询值为104的major_id必然返回空。
• 关联逻辑易丢失数据：你使用INNER JOIN关联excludeAppRoles过滤授予者为应用角色的权限，会直接过滤掉所有授予者为应用角色的权限记录，若你的业务需要这部分数据会导致结果缺失。
• 子查询逻辑不兼容多类型权限：你现有子查询未根据class_desc动态适配关联的系统表，非主体类权限（如证书、对称密钥等）的关联字段都会返回空。

修复后的查询语句 #

SELECT 'permUser1@master'                      AS dbUserName,
       dp.NAME COLLATE database_default        AS principal_name,
       dp.principal_id,
       dp.type_desc COLLATE database_default   AS principal_type_desc,
       grantor.NAME COLLATE database_default   AS grantor,
       class_desc = CASE p.class_desc
                      WHEN 'ASYMMETRIC_KEY' THEN 'ASYMMETRIC KEY'
                      WHEN 'SYMMETRIC_KEYS' THEN 'SYMMETRIC KEY'
                      ELSE p.class_desc
                    END,
       p.major_id,
       Object_schema_name(p.major_id)          fun_object_schema,
       Object_name(p.major_id, 1)              AS fun_object_name,
       -- 服务器主体类权限关联
       CASE WHEN p.class_desc = 'SERVER_PRINCIPAL' THEN 
            (SELECT NAME COLLATE database_default FROM [master].sys.server_principals WHERE principal_id = p.major_id) 
       END AS ser_object_name,
       -- 数据库主体类权限关联
       CASE WHEN p.class_desc = 'DATABASE_PRINCIPAL' THEN
            (SELECT NAME COLLATE database_default FROM [master].sys.database_principals WHERE principal_id = p.major_id)
       END AS object_name,
       -- 对称密钥类权限关联
       CASE WHEN p.class_desc IN ('SYMMETRIC_KEY','SYMMETRIC_KEYS') THEN
            (SELECT NAME COLLATE database_default FROM [master].sys.symmetric_keys WHERE symmetric_key_id = p.major_id)
       END AS symmetric_key,
       -- 非对称密钥类权限关联
       CASE WHEN p.class_desc = 'ASYMMETRIC_KEY' THEN
            (SELECT NAME COLLATE database_default FROM [master].sys.asymmetric_keys WHERE asymmetric_key_id = p.major_id)
       END AS asymmetric_key,
       -- 程序集类权限关联
       CASE WHEN p.class_desc = 'ASSEMBLY' THEN
            (SELECT NAME COLLATE database_default FROM [master].sys.assemblies WHERE assembly_id = p.major_id)
       END AS assembly_name,
       -- 证书类权限关联
       CASE WHEN p.class_desc = 'CERTIFICATE' THEN
            (SELECT NAME COLLATE database_default FROM [master].sys.certificates WHERE certificate_id = p.major_id)
       END AS certificate_name,
       -- 安全客体类型适配
       CASE p.class_desc
           WHEN 'SQL_USER' THEN 'USER'
           WHEN 'CERTIFICATE_MAPPED_USER' THEN 'USER'
           WHEN 'DATABASE_ROLE' THEN 'DATABASE ROLE'
           WHEN 'ASYMMETRIC_KEY' THEN 'ASYMMETRIC KEY'
           WHEN 'SYMMETRIC_KEYS' THEN 'SYMMETRIC KEY'
           WHEN 'CERTIFICATE' THEN 'CERTIFICATE'
           ELSE p.class_desc
       END AS securable,
       p.permission_name,
       ao.type_desc                            AS object_desc,
       p.state_desc                            AS permission_state_desc,
       dp.sid
FROM   [master].sys.database_permissions p
       INNER JOIN [master].sys.database_principals dp
               ON p.grantee_principal_id = dp.principal_id
       LEFT JOIN [master].sys.database_principals grantor
              ON p.grantor_principal_id = grantor.principal_id
       -- 若不需要应用角色授予的权限可保留该条件，否则直接删除
       LEFT JOIN [master].sys.database_principals excludeAppRoles
               ON excludeAppRoles.principal_id = grantor.principal_id
                  AND excludeAppRoles.type_desc <> 'APPLICATION_ROLE'
       LEFT JOIN [master].sys.all_objects ao
              ON p.major_id = ao.object_id
WHERE  dp.principal_id = 6 -- 替换为目标用户的principal_id
-- 若需要查询所有类型权限，移除下面的CERTIFICATE过滤条件
-- AND class_desc = 'CERTIFICATE'

使用说明 #

• 将WHERE子句中的dp.principal_id = 6替换为你实际要查询的用户ID即可返回对应结果
• 若需要查询该用户所有类型的权限，直接删除AND class_desc = 'CERTIFICATE'的过滤条件即可
• 新增的certificate_name字段会自动关联返回证书类权限对应的证书名称，你可以根据业务需要调整返回字段

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