When creating a report to show which groups have permission to execute a process, getting back more groups than expected

Document ID : KB000010853
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

I want to create a report that shows which user groups have permission to execute each of the processes in my project and state.  But the results returned show some extra user groups that do not appear in the ExecuteAccess list when looking at the properties for the process.  Why is this?  What would be the correct SQL to use?

Background:

The reason for this is that Harvest remembers every group that was added to the execute list for the process, even if that group has since been removed. 

 

Instructions:

This SQL should be able to provide a listing that only shows user groups currently granted permission to execute each process

SELECT HARENVIRONMENT.ENVIRONMENTNAME,
  HARSTATE.STATENAME,
  HARSTATEPROCESS.PROCESSNAME,
  HARSTATEPROCESS.PROCESSTYPE,
  HARUSERGROUP.USERGROUPNAME,
  HARSTATEPROCESSACCESS.EXECUTEACCESS
FROM HARENVIRONMENT
INNER JOIN HARSTATE ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID
INNER JOIN HARSTATEPROCESS ON HARSTATE.STATEOBJID = HARSTATEPROCESS.STATEOBJID
INNER JOIN HARSTATEPROCESSACCESS ON HARSTATEPROCESS.PROCESSOBJID = HARSTATEPROCESSACCESS.PROCESSOBJID
INNER JOIN HARUSERGROUP ON HARUSERGROUP.USRGRPOBJID = HARSTATEPROCESSACCESS.USRGRPOBJID
WHERE HARSTATEPROCESSACCESS.EXECUTEACCESS = 'Y'
AND HARENVIRONMENT.ENVIRONMENTNAME = 'SampleProject' /* <--- Project name goes here */
/* AND HARSTATE.STATENAME = 'Development' /* <--- State name goes here */
/* AND HARSTATEPROCESS.PROCESSNAME = 'Check Out for Browse' /* <--- Process name goes here */
ORDER BY HARENVIRONMENT.ENVIRONMENTNAME,
  HARSTATE.STATENAME,
  HARSTATEPROCESS.PROCESSNAME,
  HARUSERGROUP.USERGROUPNAME