Database Role Audit (alpha version) |

OK so this is kind of a “part 2″ to a post I made a while back about doing a SQL Server Role Audit. I wanted to go to the next level down and do the same type of thing for all database roles (system and user). This is definitely an “alpha version” of the script. It’s meant to run in SQL 2005/2008 but still work on databases running in 80 Compatibility Mode.

I would LOVE some feedback/comments/suggestions/etc… what I would love even more is a link to a better script that does the same thing

LOL remember this is v1 so I haven’t really gone back through it and made it “net worthy” but if you could give it a run, give me some feedback, etc it would be greatly appreciated

SET NOCOUNT ON DECLARE @DatabaseRoles NVARCHAR(MAX) DECLARE @SQLSTMT NVARCHAR(MAX) DECLARE @cmptlevel INT


SELECT @cmptlevel = (SELECT cmptlevel   
              FROM master.dbo.sysdatabases   
              WHERE dbid = DB_ID())  
CREATE TABLE #DatabaseRoleMatrix(  
  [DatabaseUserName] VARCHAR(256), 
  [DatabaseUserType] VARCHAR(10),  
  [DatabaseRoleName] VARCHAR (256),  
  [RoleAccess]   CHAR(1))  
INSERT INTO #DatabaseRoleMatrix([DatabaseUserName], 
     [DatabaseUserType],   
     [DatabaseRoleName], [RoleAccess])  
SELECT  NULL AS 'DatabaseUserName',  
     'Role' AS 'DatabaseUserType',   
     name AS 'DatabaseRoleName', NULL AS 'RoleAccess'  
FROM dbo.sysusers  
WHERE issqlrole = 1  
UNION ALL  
SELECT  b.name AS 'DatabaseUserName',  
     CASE 
        WHEN b.issqlrole = 1 THEN 'Role' 
        ELSE  'User'  
     END,  
     c.name AS 'DatabaseRoleName', 'X' AS 'RoleAccess'  
FROM dbo.sysusers b LEFT OUTER JOIN   
     dbo.sysmembers  a ON a.memberuid = b.uid LEFT OUTER JOIN   
     dbo.sysusers c ON a.groupuid = c.uid   
WHERE   b.name NOT IN('db_owner','db_accessadmin', 
     'db_securityadmin','public',  
 'db_ddladmin','db_backupoperator','db_datareader', 
 'db_datawriter', 
 'db_denydatareader','db_denydatawriter')   
IF @cmptlevel > 80  
BEGIN  
  SELECT @DatabaseRoles = COALESCE(@DatabaseRoles +   
        ',[' + CAST(DatabaseRoleName AS VARCHAR) + ']',  
        '[' + CAST(DatabaseRoleName AS VARCHAR)+ ']')  
  FROM #DatabaseRoleMatrix  
  WHERE DatabaseRoleName IS NOT NULL  
  GROUP BY DatabaseRoleName  
  SET @SQLSTMT = '  
SELECT db_name() as ''db_name'', *  
FROM #DatabaseRoleMatrix  
PIVOT  
(  
MAX(RoleAccess)  
FOR [DatabaseRoleName]  
IN (' + @DatabaseRoles + ')  
)  
AS p 
WHERE DatabaseUserName IS NOT NULL 
ORDER BY DatabaseUserType DESC, DatabaseUserName'  
  EXECUTE(@SQLSTMT)  
END  
IF @cmptlevel < 90  
BEGIN  
  CREATE TABLE #WhileDatabaseRole(  
     [RowNum] INT IDENTITY (1,1) PRIMARY KEY,  
      [DatabaseRoleName] NVARCHAR(256))  
    
  DECLARE @RowCount INT  
  DECLARE @DatabaseRoleList NVARCHAR(MAX)  
  DECLARE @DatabaseRoleName NVARCHAR(MAX)  
  SET @DatabaseRoleList = ''  
  INSERT INTO #WhileDatabaseRole([DatabaseRoleName])  
  SELECT   DISTINCT DatabaseRoleName   
  FROM  #DatabaseRoleMatrix   
  WHERE DatabaseRoleName IS NOT NULL  
  ORDER BY DatabaseRoleName  
    
  SELECT @RowCount = MAX([RowNum]) FROM #WhileDatabaseRole  
     SET @SQLSTMT = 'SELECT db_name() as ''db_name'',   
DatabaseUserName, DatabaseUserType,  '  
    
  WHILE @RowCount <> 0  
  BEGIN  
     SET @DatabaseRoleName = (SELECT [DatabaseRoleName]   
                        FROM #WhileDatabaseRole   
                        WHERE [RowNum] = @RowCount)  
     SET @SQLSTMT = @SQLSTMT + ' MAX( 
    CASE WHEN DatabaseRoleName = '   
              + CHAR(39) + @DatabaseRoleName + CHAR(39) +  
              ' THEN ''X'' END) AS ' +   
              CHAR(39) + @DatabaseRoleName + CHAR(39) + ','  
     DELETE FROM #WhileDatabaseRole WHERE [RowNum] = @RowCount  
     SET @RowCount = @RowCount - 1  
  END  
    
  SELECT @SQLSTMT = SUBSTRING(@SQLSTMT, 1, LEN(@SQLSTMT)-1) +  
        ' FROM #DatabaseRoleMatrix   
  WHERE DatabaseUserName IS NOT NULL   
  GROUP BY DatabaseUserName, DatabaseUserType   
  ORDER BY DatabaseUserType DESC, DatabaseUserName'  
    
  EXEC sp_executesql @SQLSTMT  
    
  DROP TABLE #WhileDatabaseRole  
    
END