如何在SQL Server中查询通用权限并生成对应T-SQL脚本

权限查询方法 #

你说的grant execute to testrole这类不绑定具体对象的权限属于数据库级通用权限，存储在SQL Server公开的系统视图中，无需查询底层未公开的系统表即可检索：

查询指定角色的数据库级通用权限 #

SELECT 
    dp.permission_name,
    dp.state_desc,
    USER_NAME(dp.grantee_principal_id) AS grantee_name
FROM sys.database_permissions dp
INNER JOIN sys.database_principals grp 
    ON dp.grantee_principal_id = grp.principal_id
WHERE 
    dp.class = 0 -- class=0标记为数据库级权限，无绑定具体对象
    AND grp.name = 'testrole' -- 替换为你要查询的角色名称

直接生成对应的授权T-SQL脚本 #

将查询结果拼接为可直接执行的授权语句：

SELECT 
    CONCAT(
        dp.state_desc, ' ', dp.permission_name, 
        ' TO ', QUOTENAME(USER_NAME(dp.grantee_principal_id)), ';'
    ) AS grant_script
FROM sys.database_permissions dp
INNER JOIN sys.database_principals grp 
    ON dp.grantee_principal_id = grp.principal_id
WHERE 
    dp.class = 0
    -- 去掉下方条件可查询当前库所有用户/角色的数据库级通用权限
    AND grp.name = 'testrole'

服务器级通用权限查询 #

如果你需要查询服务器范围的通用权限（比如grant alter any login to testrole这类），使用服务器级系统视图即可：

SELECT 
    CONCAT(
        sp.state_desc, ' ', sp.permission_name, 
        ' TO ', QUOTENAME(srp.name), ';'
    ) AS server_grant_script
FROM sys.server_permissions sp
INNER JOIN sys.server_principals srp 
    ON sp.grantee_principal_id = srp.principal_id
WHERE 
    sp.class = 100 -- class=100标记为服务器级权限
    AND srp.name = 'test_server_role'

SSMS查看方法 #

你之前在SSMS没看到这类权限是因为没选对安全对象范围：

• 打开对应数据库的【安全性】→【角色】→【数据库角色】
• 右键目标角色选择【属性】，切换到【安全对象】页
• 点击左上角【搜索】，选择【特定类型的所有对象】，勾选【数据库】后确认
• 选中搜索到的当前数据库，下方权限列表即可看到所有数据库级通用权限

内容的提问来源于stack exchange，提问作者928-5.0