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
DROP TABLE #DatabaseRoleMatrix
UPDATE: K. Brian Kelley asked if handles nested user defined database roles…. it does now
Enjoy!!
