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

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

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

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

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

这个报表报头动态地获取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

—保存为存储过程很棒

非常感谢出色的审计脚本。

我强烈建议审计用户使用很棒的Kenneth Fisher (b | t)存储过程:

sp_DBPermissions sp_SrvPermissions

我尝试了几乎所有这些功能，但很快就注意到缺少一些功能，尤其是系统管理员用户。在我们即将进行的审计中，有这样一个洞会很不好看，所以我想出了这个

USE master
GO

SELECT DISTINCT 
        p.name AS [loginname] ,
        --p.type,
        p.type_desc ,
        p.is_disabled,
        s.sysadmin,
        sp.permission_name
FROM sys.server_principals p
INNER JOIN sys.syslogins s ON p.sid = s.sid
INNER JOIN sys.server_permissions sp ON p.principal_id = sp.grantee_principal_id
WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
    -- Logins that are not process logins
    AND p.name NOT LIKE '##%'
ORDER BY p.name
GO

一个简单的查询，只显示你是否是系统管理员:

IF IS_SRVROLEMEMBER ('sysadmin') = 1  
   print 'Current user''s login is a member of the sysadmin role'  
ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0  
   print 'Current user''s login is NOT a member of the sysadmin role'  
ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL  
   print 'ERROR: The server role specified is not valid.';

从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角色授予对大多数对象的选择权限。