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.