Cross Reference All LBI Reports to Security

I've been using one I created in crystal for awhile. This is something I called report_access: -- User Access select ra.reportid, ra.accessvalue as r_user from lawson_rs.ers_reportaccess ra where instanceid = 0 and ra.accesstype in ( 'user','usergroup') union -- Security Groups select ra.reportid, sg.securitygroupname as r_user from lawson_rs.ers_reportaccess ra, lawson_rs.ers_securitygroups sg where instanceid = 0 and ra.accesstype = 'securitygroup' and sg.securitygroupid = ra.accessvalue union -- Custom Groups select ra.reportid, cgm.memberid as r_user from lawson_rs.ers_reportaccess ra, lawson_rs.ers_customgroups cg, lawson_rs.ers_customgroupmembers cgm where instanceid = 0 and ra.accesstype = 'custom' and cg.groupname = ra.accessvalue and cgm.groupid = cg.groupid

I have used the following. It does not bring in the detail of the group members but could be altered to pull in that info as well. SELECT rpt.reportid ,rpt.reporttype ,rpt.reportname ,rpt.publisher ,rpt.owner ,rpt.outputtype ,rac.accesstype ,rac.accessvalue FROM (SELECT * FROM ers_reportaccess WHERE instanceid = 0) rac JOIN (SELECT * FROM ers_reports WHERE instanceid = 0) rpt ON (rac.reportid = rpt.reportid) ORDER BY rpt.reportid ,rac.accesstype ,rac.accessvalue

H Angela, When it comes to reports you can pull users and groups who have access to the report AND/OR who have access to the Schedule of that report. (published reports, Instance=(0) and each schedule, instance=(-1) ) The query I use is very similar to Dave's. However, we don't use the CustomGroups so Greg's query would not work for us. To pull the Users assigned to usergroups, I use this query (I write views and only use crystal to make it look pretty.) However, our Username is our employee number, so we created something special to get the actual name attached to the user. Hence you will not have the GET_USERGROUP_NAME function. Hope this helps -Joan ( I forgot - you need to access the LawsonFS database not LawsonRS) select u.USERID as GROUP_NBR , u.USERNAME as GROUP_NAME , g.USERID as USER_NBR , u2.USERNAME as USER_LAW_NBR , ghs.GET_USERGROUP_NAME(u2.USERNAME) as USERNAME from ENPUSERMAP u join ENPGROUPMAP g on u.USERID = g.GROUPID join ENPUSERMAP u2 on g.USERID = u2.USERID --and u2.ENTRYTYPE = 1 where u.ENTRYTYPE = 2 --2=groups 1=users

Thanks!

One of the things I noticed is that at least one report which goes out of to many people had no users other than the owner. It appears there are users tied to the schedule. Is there a way to get that information? Perhaps, that is what Joan has with the LawsonFS database. We have users tied to reports by users, by user groups, custom groups, and custom groups on schedules.

I think I understand now. It looks like instance -1 on ers_reportaccess is for the schedule.