我只是在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

从SQL Server 2005开始，您可以使用系统视图来实现这一点。例如，这个查询列出了数据库中的所有用户及其权限:

select  princ.name
,       princ.type_desc
,       perm.permission_name
,       perm.state_desc
,       perm.class_desc
,       object_name(perm.major_id)
from    sys.database_principals princ
left join
        sys.database_permissions perm
on      perm.grantee_principal_id = princ.principal_id

请注意，用户也可以通过角色拥有权限。例如，db_data_reader角色授予对大多数对象的选择权限。

——好了，轮到我回馈了，好好享受吧

这个报表报头动态地获取SQL实例名称、日期\时间和运行报表的帐户名称，这些都是优秀的审计人员想知道的。：）

注意:如果你在Master数据库上有一个名为“environment”的扩展属性，这个值(不管你使用什么:PreProd, Development, Production, DR等)将包含在报告头中。

BEGIN

BEGIN TRY
    SET NOCOUNT ON
    SELECT 'See Messages Tab..... use  Ctrl+SHIFT+F and re-run to ''send to file'''
    DECLARE @DBName nvarchar(2000) = DB_NAME()
    DECLARE @User_Name nvarchar(200) = suser_sname()
    DECLARE @Account_Name nvarchar(200)
    DECLARE @Granted_permissions nvarchar(2000)
    DECLARE @Permission_State nvarchar(200)
    DECLARE @ParentObject nvarchar(200)
    DECLARE @env2 varchar(50) = Convert(varchar(50),(Select ServerProperty('Servername')));
    DECLARE @day varchar(50) = FORMAT (getdate(), 'dddd, MM, yyyy');
    DECLARE @clk varchar(50) = FORMAT (getdate(), 'hh:mm:ss tt') ;
    DECLARE @env1 VARCHAR(25) = (SELECT CAST(value AS varchar(25)) 
    FROM [master].[sys].fn_listextendedproperty('environment', default, default, default, default, default, default));

    PRINT '*** ' + @DBName + ' Security Audit Report ***';
    PRINT '      in the ' + @env1 + ' environment'; 
    PRINT '      on SQL Instance: ' + @env2;  
    PRINT '      '+ @day + ' at ' + @clk;
    PRINT '      run under account ' + @User_Name;
    PRINT ' '

    CREATE TABLE #GP(
        DBName NVARCHAR(200),
        Account_Name NVARCHAR(200),
        Granted_Permissions NVARCHAR(max),
        Permission_State NVARCHAR(200),
        ParentObject NVARCHAR(200)
        )
    ;WITH SampleDataR AS
        (SELECT
            DB_NAME() AS 'DBName'
            ,dp.name AS 'Account_Name'
            ,dpm.permission_name AS 'Granted_Permissions'
            ,dpm.state_desc AS 'Permission_State'
            ,dpm.class_desc AS 'ParentObject'
            , ROW_NUMBER() OVER (PARTITION BY DB_NAME(), dp.[name] ,dpm.state_desc, dpm.class_desc ORDER BY permission_name) rownum
        FROM sys.database_principals dp
            LEFT OUTER JOIN [sys].[database_permissions] dpm
            ON dp.principal_id = dpm.grantee_principal_id
        WHERE dp.type ='R'
        AND dp.sid IS NOT NULL
        AND dp.name <> 'public' 
        AND dp.name NOT LIKE 'db_a%'
        AND dp.name NOT LIKE 'db_b%'
        AND dp.name NOT LIKE 'db_d%'
        AND dp.name NOT LIKE 'db_o%'
        AND dp.name NOT LIKE 'db_s%'
        --AND dpm.class_desc = 'DATABASE'  -- remove to see schema based permissions
        ) 

        --Select * from SampleDataR

    INSERT INTO #GP
    SELECT DISTINCT 
        DBName
        ,Account_Name
        ,(SELECT Granted_Permissions + 
            CASE 
                WHEN s1.rownum = (select MAX(rownum) 
                FROM SampleDataR 
                WHERE DBName = s1.DBName AND 
                Account_Name = s1.Account_Name AND
                ParentObject = s1.ParentObject)
                THEN ' (' + Permission_State + '), '
                ELSE ' (' + Permission_State + '),  '
            END 
    FROM SampleDataR s1
    WHERE s1.DBName = s2.DBName AND 
          s1.Account_Name = s2.Account_Name AND
          s1.ParentObject = s2.ParentObject
        FOR xml path(''),type).value('(.)[1]','varchar(max)'
        ) Granted_Permissions
        ,Permission_State
        ,ParentObject
        FROM SampleDataR s2

        --Select * from #GP

    PRINT  'Assigned Role Permissions'
    PRINT ' '
    SET NOCOUNT ON
    DECLARE cur CURSOR FOR
        SELECT DISTINCT DBName, Account_Name, ParentObject, Granted_permissions 
        FROM #GP

    OPEN cur
        SET NOCOUNT ON
        FETCH NEXT FROM cur INTO @DBname, @Account_Name, @ParentObject, @Granted_permissions;
        WHILE @@FETCH_STATUS = 0
        BEGIN   
            PRINT @DBName + ', ' + @Account_Name +  ', ' + '[' + @ParentObject + '], ' + @Granted_permissions
            FETCH NEXT FROM cur INTO @DBname, @Account_Name,  @ParentObject , @Granted_permissions;
        END
    CLOSE cur;
    DEALLOCATE cur;
    SET NOCOUNT ON
    DROP Table #GP

    SET NOCOUNT ON
    DECLARE @DBName2 nvarchar(200)
    DECLARE @Account_Name2 nvarchar(200)
    DECLARE @Granted_permissions2 nvarchar(200)

    CREATE TABLE #GP2(
        DBName NVARCHAR(200),
        Account_Name NVARCHAR(200) ,
        Granted_Permissions NVARCHAR(200)
        )
    ;WITH SampleDataR AS
        (SELECT
            DB_NAME() AS 'DBName'
            ,dp.name AS 'Account_Name'
            --,dp.type
            ,dpm.permission_name
            ,ROW_NUMBER() OVER (PARTITION BY DB_NAME(), dp.[name] ORDER BY permission_name) rownum
        FROM sys.database_principals dp
            LEFT OUTER JOIN [sys].[database_permissions] dpm
            ON dp.principal_id = dpm.grantee_principal_id
                --order by dp.type
        WHERE dp.type not in ('A', 'R', 'X') --removed  'G',
        AND dp.sid is not null
        AND dp.name not in ('guest','dbo')
        ) 
    INSERT INTO #GP2 
    SELECT DISTINCT 
        DBName
        ,Account_Name
        ,(SELECT permission_name + 
            CASE 
                WHEN s1.rownum = (select MAX(rownum) 
                FROM SampleDataR 
                WHERE DBName = s1.DBName and Account_Name = s1.Account_Name
            )
            THEN '' 
            ELSE ',' 
            END 
    FROM SampleDataR s1
    WHERE s1.DBName = s2.DBName AND s1.Account_Name = s2.Account_Name
        FOR xml path(''),type).value('(.)[1]','varchar(max)') Granted_Permissions
        FROM SampleDataR s2;

    PRINT ' '
    PRINT ' '
    PRINT  'Assigned User Permissions'
    PRINT ' '
    DECLARE cur CURSOR FOR
        SELECT DBName, Account_Name, Granted_permissions 
        FROM #GP2
    OPEN cur
        SET NOCOUNT ON
        FETCH NEXT FROM cur INTO @DBname2, @Account_Name2, @Granted_permissions2;
        WHILE @@FETCH_STATUS = 0
        BEGIN   
            PRINT @DBName2 + ', ' + @Account_Name2 + ', ' + @Granted_permissions2
            FETCH NEXT FROM cur INTO @DBname2, @Account_Name2, @Granted_permissions2;
        END
    CLOSE cur;
    DEALLOCATE cur;
    DROP TABLE #GP2

    SET NOCOUNT ON
    DECLARE @DBName3 nvarchar(200)
    DECLARE @Role_Name3 nvarchar(max)
    DECLARE @Members3 nvarchar(max)

    CREATE TABLE #GP3(
        DBName NVARCHAR(200),
        Role_Name NVARCHAR(max),
        members NVARCHAR(max)
        )
    ;WITH SampleDataR AS
        (SELECT
            DB_NAME() AS 'DBName'
            ,r.name AS 'role_name'
            ,m.name AS 'members'
            ,ROW_NUMBER() OVER (PARTITION BY DB_NAME(), r.[name] ORDER BY m.[name]) rownum
        FROM sys.database_role_members rm 
            INNER JOIN sys.database_principals r on rm.role_principal_id = r.principal_id
            INNER JOIN sys.database_principals m on rm.member_principal_id = m.principal_id
        ) 

    INSERT INTO #GP3
    SELECT DISTINCT 
        DBName
        ,Role_Name
        ,(SELECT Members + 
            CASE 
                WHEN s3.rownum = (select MAX(rownum) 
                FROM SampleDataR 
                WHERE DBName = s3.DBName and Role_Name = s3.Role_Name
                )
            THEN ',' 
            ELSE ',' 
            END 
    FROM SampleDataR s1
    WHERE s1.DBName = s3.DBName and s1.Role_Name = s3.Role_Name
        FOR xml path(''),type).value('(.)[1]','varchar(max)') Members
        FROM SampleDataR s3

    PRINT ' '
    PRINT ' '
    PRINT  'Assigned Role Membership'
    PRINT ' '
    DECLARE cur CURSOR FOR
        SELECT DBName, Role_Name, Members 
        FROM #GP3
    OPEN cur
        SET NOCOUNT ON
        FETCH NEXT FROM cur INTO @DBname3, @Role_Name3, @Members3;
        WHILE @@FETCH_STATUS = 0
        BEGIN   
            PRINT @DBName3 + ', ' + @Role_Name3 + ', ' + @Members3
            FETCH NEXT FROM cur INTO @DBname3, @Role_Name3, @Members3;
        END
    CLOSE cur;
    DEALLOCATE cur;
    DROP Table #GP3;
END TRY

BEGIN CATCH  
   SELECT 'Real ERROR at Line #' + CAST(ERROR_LINE() AS VARCHAR(20));
   -- Throw/raise and error caught from the Try section. 
   THROW; 
END CATCH;

END

—保存为存储过程很棒

我看到的其他答案遗漏了数据库中可能存在的一些权限。下面代码中的第一个查询将获得非系统对象的数据库级权限。它还生成适当的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

我只是在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

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

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