Post reply

Security Audit Db_DataReader

  • April 9, 2011 at 12:23 pm

    #236491

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

  • April 11, 2011 at 7:45 pm

    #1310184

    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

  • April 12, 2011 at 8:10 am

    #1310408

    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;'

  • April 21, 2011 at 7:31 am

    #1314498

    The script has now been updated with the correction.

    Thanks!

  • May 16, 2016 at 7:19 am

    #1879238

    Thanks for the script.

  • May 16, 2016 at 7:19 am

    #1879239

    ...and thanks for the update.

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

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