很棒的脚本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]  

由于低代表不能与此回复的人要求在多个数据库/SQL服务器上运行此。

创建一个注册的服务器组，并查询他们所有的我们以下和只是游标通过数据库:

--Make sure all ' are doubled within the SQL string.

DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(max)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM MASTER.dbo.sysdatabases
where name like '%DBName%'

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  

SELECT @statement = 'use '+@dbname +';'+ '
/*
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:
UserType        : Value will be either ''SQL User'', ''Windows User'', or ''Windows Group''.
                  This reflects the type of user/group defined for the SQL Server 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.
LoginName       : SQL or Windows/Active Directory user account.  This could also be an Active Directory group.
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.
Schema          : Name of the schema the object is in.
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.
*/

    --1) List all access provisioned to a SQL user or Windows user/group directly
    SELECT
        [UserType] = CASE princ.[type]
                         WHEN ''S'' THEN ''SQL User''
                         WHEN ''U'' THEN ''Windows User''
                         WHEN ''G'' THEN ''Windows Group''
                     END,
        [DatabaseUserName] = princ.[name],
        [LoginName]        = ulogin.[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,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
    FROM
        --Database user
        sys.database_principals            AS princ
        --Login accounts
        LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = princ.[sid]
        --Permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = princ.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        princ.[type] IN (''S'',''U'',''G'')
        -- No need for these system accounts
        AND princ.[name] NOT IN (''sys'', ''INFORMATION_SCHEMA'')

UNION

    --2) List all access provisioned to a SQL user or Windows user/group through a database or application role
    SELECT
        [UserType] = CASE membprinc.[type]
                         WHEN ''S'' THEN ''SQL User''
                         WHEN ''U'' THEN ''Windows User''
                         WHEN ''G'' THEN ''Windows Group''
                     END,
        [DatabaseUserName] = membprinc.[name],
        [LoginName]        = ulogin.[name],
        [Role]             = roleprinc.[name],
        [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,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
    FROM
        --Role/member associations
        sys.database_role_members          AS members
        --Roles
        JOIN      sys.database_principals  AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
        --Role members (database users)
        JOIN      sys.database_principals  AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
        --Login accounts
        LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = membprinc.[sid]
        --Permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        membprinc.[type] IN (''S'',''U'',''G'')
        -- No need for these system accounts
        AND membprinc.[name] NOT IN (''sys'', ''INFORMATION_SCHEMA'')

UNION

    --3) List all access provisioned to the public role, which everyone gets by default
    SELECT
        [UserType]         = ''{All Users}'',
        [DatabaseUserName] = ''{All Users}'',
        [LoginName]        = ''{All Users}'',
        [Role]             = roleprinc.[name],
        [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,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
    FROM
        --Roles
        sys.database_principals            AS roleprinc
        --Role permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        --All objects
        JOIN      sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        roleprinc.[type] = ''R''
        AND roleprinc.[name] = ''public''
        AND obj.[is_ms_shipped] = 0

ORDER BY
    [UserType],
    [DatabaseUserName],
    [LoginName],
    [Role],
    [Schema],
    [ObjectName],
    [ColumnName],
    [PermissionType],
    [PermissionState],
    [ObjectType]
'
exec sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor 

这个帖子极大地帮助了我，谢谢大家!

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

以下是我的版本，改编自他人。我刚刚花了30分钟来回忆我是怎么想到这个的，@杰里米的回答似乎是我的核心灵感。我不想更新杰里米的答案，以防我引入错误，所以我在这里发布了我的版本。

我建议将完整脚本与Kenneth Fisher的《T-SQL Tuesday:特定用户拥有什么权限?》:这将允许你自底向上回答合规/审计问题，而不是自顶向下。

EXECUTE AS LOGIN = '<loginname>'

SELECT token.name AS GroupNames
FROM sys.login_token token
JOIN sys.server_principals grp
    ON token.sid = grp.sid
WHERE token.[type] = 'WINDOWS GROUP'
  AND grp.[type] = 'G'

REVERT

To understand what this covers, consider Contoso\DB_AdventureWorks_Accounting Windows AD Group with member Contoso\John.Doe. John.Doe authenticates to AdventureWorks via server_principal Contoso\DB_AdventureWorks_Logins Windows AD Group. If someone asks you, "What permissions does John.Doe have?", you cannot answer that question with just the below script. You need to then iterate through each row returned by the below script and join it to the above script. (You may also need to normalize for stale name values via looking up the SID in your Active Directory provider.)

下面是脚本，没有包含这种反向查找逻辑。

/*


--Script source found at :  http://stackoverflow.com/a/7059579/1387418
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.
PrinciaplUserName: if UserName is not blank, then UserName else DatabaseUserName
PrincipalType    : Possible values are 'SQL User', 'Windows User', 'Database Role', 'Windows Group'
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 @HideDatabaseDiagrams BIT = 1;

--List all access provisioned to a sql user or windows user/group directly 
SELECT  
    [UserName] = CASE dbprinc.[type] 
                    WHEN 'S' THEN dbprinc.[name] -- SQL User
                    WHEN 'U' THEN sprinc.[name] -- Windows User
                    WHEN 'R' THEN NULL -- Database Role
                    WHEN 'G' THEN NULL -- Windows Group
                    ELSE NULL
                 END,
    [UserType] = CASE dbprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                    WHEN 'R' THEN NULL -- Database Role
                    WHEN 'G' THEN NULL -- Windows Group
                    ELSE dbprinc.[type]
                 END,
    [PrincipalUserName] = COALESCE(
                    CASE dbprinc.[type]
                        WHEN 'S' THEN dbprinc.[name] -- SQL User
                        WHEN 'U' THEN sprinc.[name] -- Windows User
                        WHEN 'R' THEN NULL -- Database Role
                        WHEN 'G' THEN NULL -- Windows Group
                        ELSE NULL
                     END,
                     dbprinc.[name]
                 ),
    [PrincipalType] = CASE dbprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                    WHEN 'R' THEN 'Database Role'
                    WHEN 'G' THEN 'Windows Group'
                 END,
    [DatabaseUserName] = dbprinc.[name],
    [Role] = null,
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.[type_desc],--perm.[class_desc],
    [ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id),
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --database user
    sys.database_principals dbprinc  
LEFT JOIN
    --Login accounts
    sys.server_principals sprinc on dbprinc.[sid] = sprinc.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = dbprinc.[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 
    dbprinc.[type] in ('S','U')
    AND CASE
        WHEN @HideDatabaseDiagrams = 1 AND
        dbprinc.[name] = 'guest'
        AND (
            (
                obj.type_desc = 'SQL_SCALAR_FUNCTION'
                AND OBJECT_NAME(perm.major_id) = 'fn_diagramobjects'
            )
            OR (
                obj.type_desc = 'SQL_STORED_PROCEDURE'
                AND OBJECT_NAME(perm.major_id) IN
                (
                    N'sp_alterdiagram',
                    N'sp_creatediagram',
                    N'sp_dropdiagram',
                    N'sp_helpdiagramdefinition',
                    N'sp_helpdiagrams',
                    N'sp_renamediagram'
                )
            )
        )
        THEN 0
        ELSE 1
    END = 1
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 sprinc.[name]
                    WHEN 'R' THEN NULL -- Database Role
                    WHEN 'G' THEN NULL -- Windows Group
                    ELSE NULL
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                    WHEN 'R' THEN NULL -- Database Role
                    WHEN 'G' THEN NULL -- Windows Group
                 END, 
    [PrincipalUserName] = COALESCE(
                    CASE memberprinc.[type]
                        WHEN 'S' THEN memberprinc.[name]
                        WHEN 'U' THEN sprinc.[name]
                        WHEN 'R' THEN NULL -- Database Role
                        WHEN 'G' THEN NULL -- Windows Group
                        ELSE NULL
                     END,
                     memberprinc.[name]
                 ),
    [PrincipalType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                    WHEN 'R' THEN 'Database Role'
                    WHEN 'G' THEN 'Windows Group'
                 END, 
    [DatabaseUserName] = memberprinc.[name],
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id),
    [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.server_principals sprinc on memberprinc.[sid] = sprinc.[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    
    CASE
        WHEN @HideDatabaseDiagrams = 1 AND
        memberprinc.[name] = 'guest'
        AND (
            (
                obj.type_desc = 'SQL_SCALAR_FUNCTION'
                AND OBJECT_NAME(perm.major_id) = 'fn_diagramobjects'
            )
            OR (
                obj.type_desc = 'SQL_STORED_PROCEDURE'
                AND OBJECT_NAME(perm.major_id) IN
                (
                    N'sp_alterdiagram',
                    N'sp_creatediagram',
                    N'sp_dropdiagram',
                    N'sp_helpdiagramdefinition',
                    N'sp_helpdiagrams',
                    N'sp_renamediagram'
                )
            )
        )
        THEN 0
        ELSE 1
    END = 1
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT  
    [UserName] = '{All Users}',
    [UserType] = '{All Users}',
    [PrincipalUserName] = '{All Users}',
    [PrincipalType] = '{All Users}',
    [DatabaseUserName] = '{All Users}',
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id),
    [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
    AND CASE
        WHEN @HideDatabaseDiagrams = 1 AND
        roleprinc.[name] = 'public'
        AND (
            (
                obj.type_desc = 'SQL_SCALAR_FUNCTION'
                AND OBJECT_NAME(perm.major_id) = 'fn_diagramobjects'
            )
            OR (
                obj.type_desc = 'SQL_STORED_PROCEDURE'
                AND OBJECT_NAME(perm.major_id) IN
                (
                    N'sp_alterdiagram',
                    N'sp_creatediagram',
                    N'sp_dropdiagram',
                    N'sp_helpdiagramdefinition',
                    N'sp_helpdiagrams',
                    N'sp_renamediagram'
                )
            )
        )
        THEN 0
        ELSE 1
    END = 1
ORDER BY
    dbprinc.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc--perm.[class_desc]

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

/*
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] 

下面是Jeremy提交的最受欢迎的答案，但经过修改，包含了Greg Sipes提到的系统管理员和禁用标志以及log_date_time列。

两全其美?

/*
Source: https://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database


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],
    sp.is_disabled,
    s.sysadmin,
    GETDATE() AS [log_date_time]
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]
LEFT JOIN sys.syslogins s ON princ.sid = s.sid
LEFT JOIN sys.server_principals sp ON princ.name = sp.name
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],
    sp.is_disabled,
    s.sysadmin,
    GETDATE() AS [log_date_time]
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]
LEFT JOIN sys.syslogins s ON memberprinc.[sid] = s.sid
LEFT JOIN sys.server_principals sp ON memberprinc.[name] = sp.name
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],
    sp.is_disabled,
    s.sysadmin,
    GETDATE() AS [log_date_time]
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]     
LEFT JOIN sys.syslogins s ON roleprinc.sid = s.sid
LEFT JOIN sys.server_principals sp ON roleprinc.name = sp.name
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]