View all securables for roles in SQL Server database?

How can we show all the securabl开发者_开发知识库e that is added in any particular role in script?

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

Here is another one I'm using to do database refactorings, updates or backups. It also supports column level permissions. That statement generates GRANT statements. But it's fairly easy to adapt.

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