April 9, 2011 at 12:23 pm
Comments posted to this topic are about the item Security Audit Db_DataReader
April 11, 2011 at 7:45 pm
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
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
The script has now been updated with the correction.
Thanks!
May 16, 2016 at 7:19 am
Thanks for the script.
May 16, 2016 at 7:19 am
...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
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