我想在sql 2008上写一个查询,它将报告所有有权访问特定数据库的用户,或数据库中的对象,如表,视图和存储过程,直接或由于角色等。该报告将用于安全审计目的。不确定是否有人有一个查询,将完全满足我的需求,但希望能给我一个好的开始。无论是sql 2008, 2005或2000将做,我可以根据需要转换。


当前回答

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

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

—保存为存储过程很棒

其他回答

下面是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] 
CREATE PROCEDURE Get_permission 
AS 
    DECLARE @db_name  VARCHAR(200), 
            @sql_text VARCHAR(max) 

    SET @sql_text='Create table ##db_name (user_name varchar(max),' 

    DECLARE db_cursor CURSOR FOR 
      SELECT name 
      FROM   sys.databases 

    OPEN db_cursor 

    FETCH next FROM db_cursor INTO @db_name 

    WHILE @@FETCH_STATUS = 0 
      BEGIN 
          SET @sql_text=@sql_text + @db_name + ' varchar(max),' 

          FETCH next FROM db_cursor INTO @db_name 
      END 

    CLOSE db_cursor 

    SET @sql_text=@sql_text + 'Server_perm varchar(max))' 

    EXEC (@sql_text) 

    DEALLOCATE db_cursor 

    DECLARE @RoleName VARCHAR(50) 
    DECLARE @UserName VARCHAR(50) 
    DECLARE @CMD VARCHAR(1000) 

    CREATE TABLE #permission 
      ( 
         user_name    VARCHAR(50), 
         databasename VARCHAR(50), 
         role         VARCHAR(50) 
      ) 

    DECLARE longspcur CURSOR FOR 
      SELECT name 
      FROM   sys.server_principals 
      WHERE  type IN ( 'S', 'U', 'G' ) 
             AND principal_id > 4 
             AND name NOT LIKE '##%' 
             AND name <> 'NT AUTHORITY\SYSTEM' 
             AND name <> 'ONDEMAND\Administrator' 
             AND name NOT LIKE 'steel%' 

    OPEN longspcur 

    FETCH next FROM longspcur INTO @UserName 

    WHILE @@FETCH_STATUS = 0 
      BEGIN 
          CREATE TABLE #userroles_kk 
            ( 
               databasename VARCHAR(50), 
               role         VARCHAR(50) 
            ) 

          CREATE TABLE #rolemember_kk 
            ( 
               dbrole     VARCHAR(100), 
               membername VARCHAR(100), 
               membersid  VARBINARY(2048) 
            ) 

          SET @CMD = 'use ? truncate table #RoleMember_kk insert into #RoleMember_kk exec sp_helprolemember  insert into #UserRoles_kk (DatabaseName, Role) select db_name(), dbRole from #RoleMember_kk where MemberName = ''' + @UserName + '''' 

          EXEC Sp_msforeachdb 
            @CMD 

          INSERT INTO #permission 
          SELECT @UserName 'user', 
                 b.name, 
                 u.role 
          FROM   sys.sysdatabases b 
                 LEFT OUTER JOIN #userroles_kk u 
                              ON u.databasename = b.name --and u.Role='db_owner' 
          ORDER  BY 1 

          DROP TABLE #userroles_kk; 

          DROP TABLE #rolemember_kk; 

          FETCH next FROM longspcur INTO @UserName 
      END 

    CLOSE longspcur 

    DEALLOCATE longspcur 

    TRUNCATE TABLE ##db_name 

    DECLARE @d1 VARCHAR(max), 
            @d2 VARCHAR(max), 
            @d3 VARCHAR(max), 
            @ss VARCHAR(max) 
    DECLARE perm_cur CURSOR FOR 
      SELECT * 
      FROM   #permission 
      ORDER  BY 2 DESC 

    OPEN perm_cur 

    FETCH next FROM perm_cur INTO @d1, @d2, @d3 

    WHILE @@FETCH_STATUS = 0 
      BEGIN 
          IF NOT EXISTS(SELECT 1 
                        FROM   ##db_name 
                        WHERE  user_name = @d1) 
            BEGIN 
                SET @ss='insert into ##db_name(user_name) values (''' 
                        + @d1 + ''')' 

                EXEC (@ss) 

                SET @ss='update ##db_name set ' + @d2 + '=''' + @d3 
                        + ''' where user_name=''' + @d1 + '''' 

                EXEC (@ss) 
            END 
          ELSE 
            BEGIN 
                DECLARE @var            NVARCHAR(max), 
                        @ParmDefinition NVARCHAR(max), 
                        @var1           NVARCHAR(max) 

                SET @var = N'select @var1=' + @d2 
                           + ' from ##db_name where USER_NAME=''' + @d1 
                           + ''''; 
                SET @ParmDefinition = N'@var1 nvarchar(300) OUTPUT'; 

                EXECUTE Sp_executesql 
                  @var, 
                  @ParmDefinition, 
                  @var1=@var1 output; 

                SET @var1=Isnull(@var1, ' ') 
                SET @var= '  update ##db_name set ' + @d2 + '=''' + @var1 + ' ' 
                          + @d3 + ''' where user_name=''' + @d1 + '''  ' 

                EXEC (@var) 
            END 

          FETCH next FROM perm_cur INTO @d1, @d2, @d3 
      END 

    CLOSE perm_cur 

    DEALLOCATE perm_cur 

    SELECT * 
    FROM   ##db_name 

    DROP TABLE ##db_name 

    DROP TABLE #permission 

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

不幸的是,由于声誉不够,我无法评论Sean Rose的帖子,但是我不得不修改脚本的“公共”角色部分,因为它没有显示针对sys.objects的(INNER) JOIN权限。在它被更改为LEFT JOIN之后,我不得不进一步修改where子句逻辑以省略系统对象。我对公众烫发的修改查询如下。

--3) List all access provisioned to the public role, which everyone gets by default
    SELECT
        @@servername ServerName
        , db_name() DatabaseName
        , [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
        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
        roleprinc.[type] = 'R'
        AND roleprinc.[name] = 'public'
        AND isnull(obj.[is_ms_shipped], 0) = 0
        AND isnull(object_schema_name(perm.[major_id]), '') <> 'sys'

ORDER BY
    [UserType],
    [DatabaseUserName],
    [LoginName],
    [Role],
    [Schema],
    [ObjectName],
    [ColumnName],
    [PermissionType],
    [PermissionState],
    [ObjectType]

由于低代表不能与此回复的人要求在多个数据库/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 

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