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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy