如何在MSSQL中查询已授予的列级SELECT权限及授权对象？

问题描述 #

我已经为某个登录账号授予了特定表中特定列的SELECT权限，现在想要查询这些已授予的权限。我首次尝试的代码如下：

-- Specific per object rigths
SELECT T.TABLE_TYPE AS OBJECT_TYPE, T.TABLE_SCHEMA AS [SCHEMA_NAME], T.TABLE_NAME AS [OBJECT_NAME], NULLIF(P.subentity_name, '') as COLUMN_NAME, P.PERMISSION_NAME
FROM INFORMATION_SCHEMA.TABLES T
CROSS APPLY fn_my_permissions(T.TABLE_SCHEMA + '.' + T.TABLE_NAME, 'OBJECT') P
WHERE T.TABLE_NAME = 'tablename'

但这个语句无法显示被授予列权限的对象，而且SSMS也没法直接提供这类信息。请问该如何正确查询这类权限？

解决方案 #

fn_my_permissions通常仅返回当前用户的权限，若要查看其他账号的列级权限，更可靠的方式是借助SQL Server的系统目录视图关联查询：

方法一：查询指定账号的列级SELECT权限 #

SELECT
    tp.type_desc AS OBJECT_TYPE,
    s.name AS [SCHEMA_NAME],
    t.name AS [OBJECT_NAME],
    c.name AS COLUMN_NAME,
    dp.permission_name,
    dp.state_desc AS PERMISSION_STATE,
    gp.name AS GRANTEE_NAME
FROM
    sys.database_permissions dp
JOIN
    sys.database_principals gp ON dp.grantee_principal_id = gp.principal_id
JOIN
    sys.tables t ON dp.major_id = t.object_id
JOIN
    sys.schemas s ON t.schema_id = s.schema_id
JOIN
    sys.columns c ON dp.major_id = c.object_id AND dp.minor_id = c.column_id
WHERE
    dp.permission_type = 'SL' -- SELECT权限的类型代码
    AND t.name = 'tablename' -- 替换为目标表名
    AND gp.name = 'your_target_user' -- 替换为要查询的账号名
ORDER BY
    s.name, t.name, c.name;

方法二：查询当前用户的列级SELECT权限 #

如果只需要查看当前登录用户自己的列级权限，可以调整fn_my_permissions的用法：

SELECT
    OBJECT_TYPE = 'TABLE',
    SCHEMA_NAME = OBJECT_SCHEMA_NAME(major_id),
    OBJECT_NAME = OBJECT_NAME(major_id),
    COLUMN_NAME = COL_NAME(major_id, minor_id),
    permission_name,
    state_desc AS PERMISSION_STATE
FROM
    fn_my_permissions(NULL, 'DATABASE')
WHERE
    class_desc = 'OBJECT_OR_COLUMN'
    AND permission_name = 'SELECT'
    AND OBJECT_NAME(major_id) = 'tablename'; -- 替换为目标表名

关键说明 #

• sys.database_permissions：存储数据库内所有权限记录，当minor_id不为0时，代表该权限是列级权限（对象级权限的minor_id为0）
• sys.columns：通过表的object_id（对应major_id）和列的column_id（对应minor_id）关联，精准获取被授权的列名
• sys.database_principals：用来关联得到被授予权限的账号名称，避免直接显示不易识别的principal_id
• 若要查询所有账号的列级SELECT权限，只需删除方法一中AND gp.name = 'your_target_user'这个过滤条件

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