Security Audit Db_DataReader

  • Hawkeye_DBA

    SSCarpal Tunnel

    Points: 4271

    Comments posted to this topic are about the item Security Audit Db_DataReader

  • luckysql.kinda

    SSCertifiable

    Points: 7816

    can't run the script

    Msg 55555, Level 16, State 1, Procedure sp_MSforeach_worker, Line 92

    sp_MSforeach_worker assert failed: command too long

  • Hawkeye_DBA

    SSCarpal Tunnel

    Points: 4271

    Hi, it looks like something happened when the script was posted, here it is again in full:

    EXEC sp_MSforeachdb @command1='USE ?

    DECLARE @Roles varchar(200)

    SET @Roles = ''db_datareader, Init_Role, Viewer''

    DECLARE @sqlcmd1 nvarchar(100)

    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(100);

    CREATE TABLE #temp_rolemember(DbRole varchar(25), MemberName varchar(50), 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;'

  • Hawkeye_DBA

    SSCarpal Tunnel

    Points: 4271

    The script has now been updated with the correction.

    Thanks!

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    ...and thanks for the update.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply