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

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

Description:

Symptom:

For all users created by XOG the Favorites menu is not configurable and throwing error when clicking on Configure:

Error 401 Not Authorized

This can be caused by a known CA PPM issue:

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

Details regarding this issue can be found in Knowledgebase Article #TEC597397

Other root causes could be triggering this in later versions as well.

Solution:

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 Autocommit 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.idTEC605324.zip
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 CA Support.

Disclaimer:

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

 

File Attachments:
TEC605324.zip