There are queries at our site that were created by a User who is no longer with the company. I would like to have another User have the authorization to update and execute these queries.
The SHR method for a query must be set to U.
This parameter indicates the authorization within Report Facility for others to use and/or update the object.
Valid parameters are: Y = others can use - but not update - your object. U = others can use - and update - your object. N = others cannot reference or copy your object. You will not see objects created by others with a Share option of N.
This information is stored in the PTPRF_LIB_ver table. The specific column name is PRF_SHARE_CODE.
The following query can be used to identify the queries and forms that require their share code to be updated.
SELECT PRF_ID, PRF_TYPE, PRF_NAME, PRF_SHARE_CODE FROM PTI.PTPRF_LIB_ver WHERE PRF_ID - 'xxxxxxx' <==== user ID no longer available AND PRF_TYPE IN ('QERY', 'FORM') AND PRF_SHARE_CODE IN ('N', 'Y');
Once these queries and forms are identified, then the Share code can be updated with the following SQL.
UPDATE PTI.PTPRF_LIB_ver SET PTF_SHARE_CODE = 'U' WHERE PRF_ID = 'xxxxxxxx' <==== user ID no longer available AND PRF_TYPE IN ('QERY', 'FORM') AND PRF_SHARE_CODE IN ('N', 'Y') ;