03-19-2014 04:20 AM
Can any one pelase let me know the SQL query to pull the list of Security groups assigned to project as stake holders. I am able to get the list of Resources but not groups. Attached screenshot.
Solved! Go to Solution.
03-19-2014 07:11 AM - edited 03-19-2014 07:13 AM
Here you go... the SQL shows the users and the users in the security group that are assigned as the stakeholder.
The second part of the query list the security group users. You can modify this to simply get the security group name or the ID.
SELECT DISTINCT gpu.user_id, 'User' TYPE, 'N/A' security_group FROM itg_trustee t, itg_group_participant_users gpu, pm_projects p WHERE p.project_id = <project_id> AND t.project_type_id = p.project_type_id AND t.trustee_type_code = 1 AND gpu.trustee_id = t.trustee_id UNION /* Query for Security group*/ SELECT DISTINCT us.user_id, 'User from Security Group' TYPE, ks.security_group_name security_group FROM knta_user_security us, itg_trustee t, itg_security_group_trustees sgt, pm_projects p, knta_security_groups_nls ks WHERE p.project_id = <project_id> AND t.project_type_id = p.project_type_id AND t.trustee_type_code = 1 AND sgt.trustee_id = t.trustee_id AND us.security_group_id = sgt.security_group_id AND ks.security_group_id = us.security_group_id;
-- Remember to give Kudos to answers! (click the KUDOS star)