Security Audit Db_DataReader

--Find db_dbreader roles with permissions other than Select or Connect for each database
--Author: HawkeyeDB
--Date: 3/29/2011
--Revision: First
--Purpose: Find db_dbreader roles with permissions other than Select or Connect for each database
--Recommendations: Run this in a non-production environment first, the script uses dynamic SQL and an 
--                         undocumented stored procedure
--Find db_dbreader roles with permissions other than Select or Connect for each database
--Author: HawkeyeDB
--Date: 3/29/2011
--Revision: First
--Purpose: Find db_dbreader roles with permissions other than Select or Connect for each database
--Recommendations: Run this in a non-production environment first, the script uses dynamic SQL and an 
--                 undocumented stored procedure

EXEC sp_MSforeachdb @command1='USE [?]

DECLARE @Roles varchar(200)
SET @Roles = ''db_datareader, Init_Role, Viewer''

DECLARE @sqlcmd1 nvarchar(500)
CREATE TABLE #temp_helprotect(Owner varchar(50), Object varchar(500), 
Grantee varchar(50), Grantor varchar(50), 
ProtectType varchar(50), Action varchar(50), RefColumn varchar(1000));

SET @sqlcmd1 = ''EXEC [?]..sp_helprotect'';

INSERT INTO #temp_helprotect EXECUTE(@sqlcmd1); 

DECLARE @sqlcmd2 nvarchar(200);

CREATE TABLE #temp_rolemember(DbRole varchar(50), MemberName varchar(100), MemberSID nvarchar(1000));

SET @sqlcmd2 = ''EXEC [?]..sp_helprolemember'';

INSERT INTO #temp_rolemember EXECUTE(@sqlcmd2);

SELECT DbRole, MemberName
INTO #dbreaders
FROM #temp_rolemember
WHERE MemberName NOT IN(Select MemberName FROM #temp_rolemember WHERE RTRIM(LTRIM(DbRole)) NOT IN(''db_datareader'', ''Init_role'', ''Viewer''))

SELECT DISTINCT
A.MemberName, B.Grantee, B.Object, B.Grantor, B.ProtectType, B.Action, C.name 
FROM #dbreaders A, #temp_helprotect B, sys.sysdatabases C, #temp_rolemember D
WHERE 
A.MemberName = D.MemberName
AND A.MemberName LIKE ''%'' + B.Grantee + ''%''
AND Action Not In(''Select'', ''Connect'')
AND C.dbid = DB_ID()
ORDER BY A.MemberName, B.Grantee;

DROP TABLE #temp_rolemember;
DROP TABLE #dbreaders;
DROP TABLE #temp_helprotect;'