Missing Favorites for XOG imported users and other users that have an issue to access/edit favorites

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Some users are unable to configure the Favorites menu. When attempting to configure the menu by clicking on the Configure link, the following error is thrown: 

Error 401 - Unauthorized. You are not authorized to view the page. If you are sure you have access, try logging in again or contact your system administrator. 

Environment

Release: All Clarity Releases 

Cause

This can be caused by a known Clarity PPM issue in earlier versions:

CLRT-66180 - XOG: when we xog in resources first, then users, the users don't get the Users Favorites Menu - Edit' right

 

Resolution

Fixed in Version 13.2 and any left over records can be fixed using the below workaround 

Steps to fix the issue:

1. On your Test environment, recreate the issue with few of the affected users
2. Make sure you always use UTF8 encoding when executing the script (verify the captions in the script are displaying properly for all not English characters beforehand)
3. Connect to your Test database and make sure Auto commit is disabled in your database query tool
4. Run the appropriate script for your database (Oracle or MSSQL server) - ATTACHED to this kb article
5. Once script has finished executing, make sure you see no database specific errors occurred in the query output. Then commit the changes
6. Reconnect and test with same users as in step 1.
7. When confirmed that everything is fine, apply to higher environments

This script can also be used for users that have not been created by XOG for which Favorites menu is not opening at all when scrolling over or giving error 401. The root cause for the issue will be different.

If you need to only correct one user please modify the select condition before step 3. in order to include the specific user. Example:

This part:

select s.user_id from prj_resources r, srm_resources s, cmn_sec_users u 
where  r.prid = s.id
and s.user_id = u.id
and s.is_active=1
and u.user_status_id = 200
order by s.user_id;

can be modified to:

select s.user_id from prj_resources r, srm_resources s, cmn_sec_users u 
where  r.prid = s.id
and s.user_id = u.id
and s.is_active=1
and u.user_status_id = 200
and s.unique_name='{indicate here the resource username}'
order by s.user_id

The above will include only the specific user, provided that the resource is active.

If you need any assistance with this, please contact Broadcom Support.

Disclaimer: This script is tested on environments under 25k users. For more users affected, please contact Broadcom Support for assistance with running the solution.