在SQL Server数据库中查看角色的所有可保护对象？

Question

我们如何在脚本中显示添加到任何特定角色的所有可保护对象？

- sanjeev40084

Answer 1

SELECT
    OBJECT_NAME(major_id), USER_NAME(grantee_principal_id), permission_name
FROM
    sys.database_permissions p
WHERE
    p.class = 1 AND
    OBJECTPROPERTY(major_id, 'IsMSSHipped') = 0
ORDER BY
    OBJECT_NAME(major_id), USER_NAME(grantee_principal_id), permission_name

- gbn

---

你是否恰好有这段代码的SQL 2000版本？ - Geoff Dawdy

能否对此进行过滤以添加一个列来说明对象类型？例如：表、视图、存储过程等。 - Adam

Answer 2

这是我用来进行数据库重构、更新或备份的另一个工具。它还支持列级权限。该语句生成GRANT语句。但很容易进行调整。

SELECT (case when state_desc like 'GRANT%' then 'GRANT' else state_desc end)
  + ' ' + database_permissions.permission_name 
  + CASE database_permissions.class_desc
        WHEN 'SCHEMA' THEN ' ON SCHEMA::[' + schema_name(major_id) + ']'
        WHEN 'OBJECT_OR_COLUMN' THEN ' ON ' 
          + isnull('[' + schema_name(objects.schema_id) + '].', '') + '['
          + (CASE WHEN minor_id = 0 THEN object_name(major_id)  + ']' COLLATE Latin1_General_CI_AS_KS_WS
            ELSE (SELECT object_name(object_id) + '] (['+ name + '])'
                  FROM sys.columns 
                  WHERE object_id = database_permissions.major_id 
                  AND column_id = database_permissions.minor_id) end)
        WHEN 'DATABASE_PRINCIPAL' THEN ' ON USER::[' + USER_NAME(major_id) + ']'
        WHEN 'DATABASE' Then ''
        WHEN 'SERVICE_CONTRACT' then ' ON CONTRACT::[' 
          + (select name 
             from sys.service_contracts 
             where service_contract_id = major_id) 
          + ']'
        ELSE ' <<' + database_permissions.class_desc + '>>'
    END
  + ' TO [' + database_principals.name + ']'
  + (case when state_desc = 'GRANT_WITH_GRANT_OPTION' then ' WITH GRANT OPTION' else '' end)
  COLLATE Latin1_General_CI_AS_KS_WS
FROM sys.database_permissions
inner JOIN sys.database_principals
ON database_permissions.grantee_principal_id = database_principals.principal_id
LEFT JOIN sys.objects
ON objects.object_id = database_permissions.major_id
WHERE database_permissions.major_id > 0

- Mario Eis