我只是在Jeremy的回答中添加了以下内容，因为我有一个分配给数据库db_datareader的角色，该角色没有显示该角色拥有的权限。我试着查看每个人帖子中的所有答案，但找不到任何可以做到这一点的答案，所以我添加了自己的问题。

    SELECT 
    UserType='Role', 
    DatabaseUserName = '{Role Members}',
    LoginName = DP2.name,
    Role = DP1.name,
    'SELECT' AS [PermissionType] ,
    [PermissionState]  = 'GRANT',
    [ObjectType] = 'Table',
    [Schema] = 'dbo',
    [ObjectName] = 'All Tables',
    [ColumnName] = NULL
FROM sys.database_role_members AS DRM  
RIGHT OUTER JOIN sys.database_principals AS DP1  
    ON DRM.role_principal_id = DP1.principal_id  
LEFT OUTER JOIN sys.database_principals AS DP2  
    ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R'
AND DP2.name IS NOT NULL

我看到的其他答案遗漏了数据库中可能存在的一些权限。下面代码中的第一个查询将获得非系统对象的数据库级权限。它还生成适当的GRANT语句。第二个查询获取所有角色成员。

这必须为每个数据库运行，但是太长了，不能用于sp_MSforeachdb。如果要这样做，就必须将它作为系统存储过程添加到主数据库中。

为了涵盖所有可能性，还必须有一个检查服务器级权限的脚本。

SELECT DB_NAME() AS database_name
    , class
    , class_desc
    , major_id
    , minor_id
    , grantee_principal_id
    , grantor_principal_id
    , databasepermissions.type
    , permission_name
    , STATE
    , state_desc
    , granteedatabaseprincipal.name AS grantee_name
    , granteedatabaseprincipal.type_desc AS grantee_type_desc
    , granteeserverprincipal.name AS grantee_principal_name
    , granteeserverprincipal.type_desc AS grantee_principal_type_desc
    , grantor.name AS grantor_name
    , granted_on_name
    , permissionstatement + N' TO ' + QUOTENAME(granteedatabaseprincipal.name) + CASE 
        WHEN STATE = N'W'
            THEN N' WITH GRANT OPTION'
        ELSE N''
        END AS permissionstatement
FROM (
    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(CONVERT(NVARCHAR(MAX), DB_NAME())) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS permissionstatement
    FROM sys.database_permissions
    WHERE (sys.database_permissions.class = 0)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.schemas.name) + N'.' + QUOTENAME(sys.objects.name) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ' + QUOTENAME(sys.schemas.name) + N'.' + QUOTENAME(sys.objects.name) + COALESCE(N' (' + QUOTENAME(sys.columns.name) + N')', N'') AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.objects
        ON sys.objects.object_id = sys.database_permissions.major_id
    INNER JOIN sys.schemas
        ON sys.schemas.schema_id = sys.objects.schema_id
    LEFT OUTER JOIN sys.columns
        ON sys.columns.object_id = sys.database_permissions.major_id
            AND sys.columns.column_id = sys.database_permissions.minor_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 1)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.schemas.name) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON SCHEMA::' + QUOTENAME(sys.schemas.name) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.schemas
        ON sys.schemas.schema_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 3)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(targetPrincipal.name) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ' + targetPrincipal.type_desc + N'::' + QUOTENAME(targetPrincipal.name) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.database_principals AS targetPrincipal
        ON targetPrincipal.principal_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 4)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.assemblies.name) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASSEMBLY::' + QUOTENAME(sys.assemblies.name) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.assemblies
        ON sys.assemblies.assembly_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 5)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.types.name) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON TYPE::' + QUOTENAME(sys.types.name) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.types
        ON sys.types.user_type_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 6)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.types.name) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON TYPE::' + QUOTENAME(sys.types.name) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.types
        ON sys.types.user_type_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 6)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.xml_schema_collections.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON XML SCHEMA COLLECTION::' + QUOTENAME(sys.xml_schema_collections.name) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.xml_schema_collections
        ON sys.xml_schema_collections.xml_collection_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 10)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.service_message_types.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON MESSAGE TYPE::' + QUOTENAME(sys.service_message_types.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.service_message_types
        ON sys.service_message_types.message_type_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 15)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.service_contracts.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON CONTRACT::' + QUOTENAME(sys.service_contracts.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.service_contracts
        ON sys.service_contracts.service_contract_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 16)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.services.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON SERVICE::' + QUOTENAME(sys.services.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.services
        ON sys.services.service_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 17)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.remote_service_bindings.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON REMOTE SERVICE BINDING::' + QUOTENAME(sys.remote_service_bindings.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.remote_service_bindings
        ON sys.remote_service_bindings.remote_service_binding_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 18)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.routes.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ROUTE::' + QUOTENAME(sys.routes.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.routes
        ON sys.routes.route_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 19)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.symmetric_keys.name) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASYMMETRIC KEY::' + QUOTENAME(sys.symmetric_keys.name) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.symmetric_keys
        ON sys.symmetric_keys.symmetric_key_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 24)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.certificates.name) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON CERTIFICATE::' + QUOTENAME(sys.certificates.name) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.certificates
        ON sys.certificates.certificate_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 25)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.asymmetric_keys.name) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASYMMETRIC KEY::' + QUOTENAME(sys.asymmetric_keys.name) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.asymmetric_keys
        ON sys.asymmetric_keys.asymmetric_key_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 26)
    ) AS databasepermissions
INNER JOIN sys.database_principals AS granteedatabaseprincipal
    ON granteedatabaseprincipal.principal_id = grantee_principal_id
LEFT OUTER JOIN sys.server_principals AS granteeserverprincipal
    ON granteeserverprincipal.sid = granteedatabaseprincipal.sid
INNER JOIN sys.database_principals AS grantor
    ON grantor.principal_id = grantor_principal_id
ORDER BY grantee_name, granted_on_name

SELECT roles.name AS role_name
    , roles.principal_id
    , roles.type AS role_type
    , roles.type_desc AS role_type_desc
    , roles.is_fixed_role AS role_is_fixed_role
    , memberdatabaseprincipal.name AS member_name
    , memberdatabaseprincipal.principal_id AS member_principal_id
    , memberdatabaseprincipal.type AS member_type
    , memberdatabaseprincipal.type_desc AS member_type_desc
    , memberdatabaseprincipal.is_fixed_role AS member_is_fixed_role
    , memberserverprincipal.name AS member_principal_name
    , memberserverprincipal.type_desc member_principal_type_desc
    , N'ALTER ROLE ' + QUOTENAME(roles.name) + N' ADD MEMBER ' + QUOTENAME(memberdatabaseprincipal.name) AS AddRoleMembersStatement
FROM sys.database_principals AS roles
INNER JOIN sys.database_role_members
    ON sys.database_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.database_principals AS memberdatabaseprincipal
    ON memberdatabaseprincipal.principal_id = sys.database_role_members.member_principal_id
LEFT OUTER JOIN sys.server_principals AS memberserverprincipal
    ON memberserverprincipal.sid = memberdatabaseprincipal.sid
ORDER BY role_name
    , member_name

更新:以下查询将检索服务器级别的权限和成员。

SELECT sys.server_permissions.class
        , sys.server_permissions.class_desc
        , sys.server_permissions.major_id
        , sys.server_permissions.minor_id
        , sys.server_permissions.grantee_principal_id
        , sys.server_permissions.grantor_principal_id
        , sys.server_permissions.type
        , sys.server_permissions.permission_name
        , sys.server_permissions.state
        , sys.server_permissions.state_desc
        , granteeserverprincipal.name AS grantee_principal_name
        , granteeserverprincipal.type_desc AS grantee_principal_type_desc
        , grantorserverprinicipal.name AS grantor_name
        , CASE 
            WHEN sys.server_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.server_permissions.state_desc
            END + N' ' + sys.server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' TO ' + QUOTENAME(granteeserverprincipal.name) AS permissionstatement
FROM sys.server_principals AS granteeserverprincipal
INNER JOIN sys.server_permissions
    ON sys.server_permissions.grantee_principal_id = granteeserverprincipal.principal_id
INNER JOIN sys.server_principals AS grantorserverprinicipal
    ON grantorserverprinicipal.principal_id = sys.server_permissions.grantor_principal_id
ORDER BY granteeserverprincipal.name
    , sys.server_permissions.permission_name

SELECT roles.name AS server_role_name
    , roles.principal_id
    , roles.type AS role_type
    , roles.type_desc AS role_type_desc
    , roles.is_fixed_role AS role_is_fixed_role
    , memberserverprincipal.name AS member_principal_name
    , memberserverprincipal.principal_id AS member_principal_id
    , memberserverprincipal.type AS member_principal_type
    , memberserverprincipal.type_desc AS member_principal_type_desc
    , memberserverprincipal.is_fixed_role AS member_is_fixed_role
    , N'ALTER SERVER ROLE ' + QUOTENAME(roles.name) + N' ADD MEMBER ' + QUOTENAME(memberserverprincipal.name) AS AddRoleMembersStatement
FROM sys.server_principals AS roles
INNER JOIN sys.server_role_members
    ON sys.server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.server_principals AS memberserverprincipal
    ON memberserverprincipal.principal_id = sys.server_role_members.member_principal_id
WHERE roles.type = N'R'
ORDER BY server_role_name
    , member_principal_name

无法评论已接受的答案，所以我将在这里添加一些评论:

I second Brad on schemas issue. From MS reference sys.objects table contains only schema-scoped objects. So to get info about "higher level" objects (i.e. schemas in our case) you need to use sys.schemas table. For [ObjectType] it's better to use obj.type_desc only for OBJECT_OR_COLUMN permission class. For all other cases use perm.[class_desc] Another type of permission which is not handled so well with this query is IMPERSONATE. To get info about impersonations one should LEFT JOIN with sys.database_principals on perm.major_id = imp.principal_id With my experience it's better to replace sys.login_token with sys.server_principals as it will show also SQL Logins, not only Windows ones One should add 'G' to allowed principal types to allow Windows groups Also, one can exclude users sys and INFORMATION_SCHEMA from resulting table, as these users are used only for service

我将发布第一个带有所有建议修复的脚本，其他部分也应该被更改:

SELECT  
    [UserName] = ulogin.[name],
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                    WHEN 'G' THEN 'Windows Group'
                 END,  
    [DatabaseUserName] = princ.[name],       
    [Role] = null,      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = CASE perm.[class] 
                        WHEN 1 THEN obj.type_desc               -- Schema-contained objects
                        ELSE perm.[class_desc]                  -- Higher-level objects
                   END,       
    [ObjectName] = CASE perm.[class] 
                        WHEN 1 THEN OBJECT_NAME(perm.major_id)  -- General objects
                        WHEN 3 THEN schem.[name]                -- Schemas
                        WHEN 4 THEN imp.[name]                  -- Impersonations
                   END,
    [ColumnName] = col.[name]
FROM    
    --database user
    sys.database_principals princ  
LEFT JOIN
    --Login accounts
    sys.server_principals ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
LEFT JOIN
    sys.schemas schem ON schem.[schema_id] = perm.[major_id]
LEFT JOIN
    sys.database_principals imp ON imp.[principal_id] = perm.[major_id]
WHERE 
    princ.[type] IN ('S','U','G') AND
    -- No need for these system accounts
    princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

这是我第一次根据安多马的建议回答问题。此查询旨在提供用户直接应用到用户帐户或通过该帐户应用的权限列表 用户拥有的角色。

/*
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly 
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role

Columns Returned:
UserName        : SQL or Windows/Active Directory user account.  This could also be an Active Directory group.
UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the 
                  SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
                  same as the server user.
Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
                  on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
                  DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE, 
                  SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.   
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.          
ObjectName      : Name of the object that the user/role is assigned permissions on.  
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
                  is only populated if the object is a table, view or a table value function.                 
*/

--List all access provisioned to a sql user or windows user/group directly 
SELECT  
    [UserName] = CASE princ.[type] 
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,  
    [DatabaseUserName] = princ.[name],       
    [Role] = null,      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],       
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --database user
    sys.database_principals princ  
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE 
    princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT  
    [UserName] = CASE memberprinc.[type] 
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END, 
    [DatabaseUserName] = memberprinc.[name],   
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],   
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT  
    [UserName] = '{All Users}',
    [UserType] = '{All Users}', 
    [DatabaseUserName] = '{All Users}',       
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],  
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Roles
    sys.database_principals roleprinc
LEFT JOIN        
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]                   
JOIN 
    --All objects   
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    roleprinc.[type] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
ORDER BY
    princ.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc--perm.[class_desc] 

如果你想检查访问数据库的特定登录已经使用这个简单的脚本如下所示:

sys。sp_helpogins @LoginNamePattern = '域\登录'——sysname

很棒的脚本Jeremy和贡献者!谢谢!

我有很多用户，所以为所有用户运行这个程序简直是一场噩梦。我不能添加评论，所以我发布了整个脚本的变化。我添加了一个变量+ where子句，这样我就可以搜索用户名中匹配最多5个字符的任何内容(或当留空时所有用户)。没什么特别的，但我认为在某些用例中会有帮助。

DECLARE @p_userName NVARCHAR(5) = 'UName' -- Specify up to five characters here (or none for all users)

/*
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly 
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role

Columns Returned:
UserName        : SQL or Windows/Active Directory user cccount.  This could also be an            Active Directory group.
UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the  SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the same as the server user.
Role            : The role name.  This will be null if the associated permissions to the object are defined at directly on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT, DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc. This value may not be populated for all roles.  Some built in roles have implicit permission definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc. This value may not be populated for all roles.  Some built in roles have implicit permission definitions.
ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE, SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc. This value may not be populated for all roles.  Some built in roles have implicit permission definitions.          
ObjectName      : Name of the object that the user/role is assigned permissions on. This value may not be populated for all roles.  Some built in roles have implicit permission definitions.
ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value is only populated if the object is a table, view or a table value function. 

*/

DECLARE @userName NVARCHAR(4) = @p_UserName + '%'
--List all access provisioned to a sql user or windows user/group directly 

SELECT  
[UserName] = CASE princ.[type] 
                WHEN 'S' THEN princ.[name]
                WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
             END,
[UserType] = CASE princ.[type]
                WHEN 'S' THEN 'SQL User'
                WHEN 'U' THEN 'Windows User'
             END,  
[DatabaseUserName] = princ.[name],       
[Role] = null,      
[PermissionType] = perm.[permission_name],       
[PermissionState] = perm.[state_desc],       
[ObjectType] = obj.type_desc,--perm.[class_desc],       
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM    
--database user
sys.database_principals princ  
LEFT JOIN
--Login accounts
sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN        
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
--Table columns
sys.columns col ON col.[object_id] = perm.major_id 
                AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE 
princ.[type] in ('S','U')  
AND princ.[name] LIKE @userName  -- Added this line --CSLAGLE
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT  
[UserName] = CASE memberprinc.[type] 
                WHEN 'S' THEN memberprinc.[name]
                WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
             END,
[UserType] = CASE memberprinc.[type]
                WHEN 'S' THEN 'SQL User'
                WHEN 'U' THEN 'Windows User'
             END, 
[DatabaseUserName] = memberprinc.[name],   
[Role] = roleprinc.[name],      
[PermissionType] = perm.[permission_name],       
[PermissionState] = perm.[state_desc],       
[ObjectType] = obj.type_desc,--perm.[class_desc],   
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM    
--Role/member associations
sys.database_role_members members
JOIN
--Roles
sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
--Role members (database users)
sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
--Login accounts
sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN        
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns col on col.[object_id] = perm.major_id 
                AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE memberprinc.[name] LIKE @userName -- Added this line --CSLAGLE
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT  
[UserName] = '{All Users}', 
[UserType] = '{All Users}', 
[DatabaseUserName] = '{All Users}',       
[Role] = roleprinc.[name],      
[PermissionType] = perm.[permission_name],       
[PermissionState] = perm.[state_desc],       
[ObjectType] = obj.type_desc,--perm.[class_desc],  
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM    
--Roles
sys.database_principals roleprinc
LEFT JOIN        
--Role permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns col on col.[object_id] = perm.major_id 
                AND col.[column_id] = perm.[minor_id]                   
JOIN 
--All objects   
sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
--Only roles
roleprinc.[type] = 'R' AND
--Only public role
roleprinc.[name] = 'public' AND
--Only objects of ours, not the MS objects
obj.is_ms_shipped = 0
ORDER BY
princ.[Name],
OBJECT_NAME(perm.major_id),
col.[name],
perm.[permission_name],
perm.[state_desc],
obj.type_desc--perm.[class_desc]