For ASP reports:
Tables: MultiPurposeResource, MultiPurposeRoles, reportdefinitiondescription
- To list report access by role
select rdd.name, r.name
from reportdefinitiondescription rdd join Multipurposeroles rr on rdd.reportidentifier = rr.ID join Role r on rr.roleid = r.roleid order by r.name
- To list report access by resource
select rdd.name, r.name
from reportdefinitiondescription rdd join MultiPurposeResource rr on rdd.reportidentifier = rr.ID join resources r on rr.resourceid = r.resourceid order by r.name
For Report Designer reports:
Tables: MultiPurposeResource, MultiPurposeRoles, report
- To list report access by role
select rdd.name, r.name
from report rdd join Multipurposeroles rr on rdd.reportid = rr.ID join Role r on rr.roleid = r.roleid order by r.name
- To list report access by resource
select rdd.name, r.name
from report rdd join MultiPurposeResource rr on rdd.reportid = rr.ID join resources r on rr.resourceid = r.resourceid order by r.name
For Cognos reports:
It is not possible use a SQL query to list which reports resources have access to, permissions are defined in Cognos.
Note that Cognos reports using Changepoint Data Model will take into account Changepoint access permissions to the entities ( engagements, projects, workgroups, etc...) whereas reports using Changepoint Unsecured Data Model will collect the data directly bypassing Changepoint access permissions.