Transferring Ownership of Scheduled Report Jobs to Other Users in the Organization

User Requirement

The User has a situation where one user is about to retire and wants to transfer the ownership of all his scheduled reports to another user. The User is asking whether “there is any recommended way to transfer scheduled reports to other users within the organization” to “save a lot of work”. Otherwise, the new user would essentially have to reproduce the existing jobs from scratch.

Recommendation

The report job ownership is identified by userID. There's no existing feature using the JasperReports Server web UI to transfer job ownership from one user to another.

Our recommendation in this situation, is to use a database utility or tool (such as SQuirrels) to manually alter the job ownership in the JasperReports Server repository database. The following process has been tested and it should work with a PostgreSQL repository database:

Log in to the JasperReports Server web UI as superuser;
Review report job in the scheduler web UI and write down the job ID of one of the job that you intend to transfer the ownership;
In the JasperReports Server “Manage User” web UI, write down user name and organization name of the user that you intend to transfer the job ownership to;
Consult with your DBA to back up your repository database before proceeding to run the batch update in your production environment;
After performing a database backup, connect to the JasperReports Server repository database using a database tool;

Run following SQL query to transfer job ownership from one user to another:

update jireportjob 
   set owner = (
                 select u.id 
                   from jiuser u
                  inner join jitenant t
                          on t.id = u.tenantid and u.username = '{to_user}'
                                               and t.tenantid = '{to_user_organization}'
               )
 where id IN   (
                 select r.id
                   from jireportjob r
                  inner join jireportjob r2
                          on r2.owner = r.owner and r2.id = {jobid}
               );

{to_user} is the user name identified in step 3
{to_user_organization} is the organization name identified in step 3
{jobid} is the one collected in step 2

Example:

User1 under organization_1 scheduled a report job “test 1” with job Id 4762:

User2 under organization_1 cannot see report job “test 1” owned by User1:

Only users with administrator role can view all jobs owned by different users:

To transfer the ownership of all report jobs that have the same owner as report job “test 1” to User2, first identify User2 username and organization name, “user2” and “organization_1”:

Then using a database utility (Squirrel, in this example), execute following SQL query to transfer all jobs that belong to the User1 (who created “test 1” job) to User2:

update jireportjob
   set owner = (
                 select u.id
                   from jiuser u
                  inner join jitenant t
                          on t.id = u.tenantid and u.username = 'user2'
                                               and t.tenantid = 'organization_1'
               )
 where id IN   (
                 select r.id
                   from jireportjob r
                  inner join jireportjob r2
                          on r2.owner = r.owner and r2.id = 4762
               );

The following screen shots are of the utility panel before and after batch update images:

Now, User2 under organization_1 is able to see report job “test 1” previously owned by User1:

Important Note:

After the ownership change, user will need to login as the new job owner, edit report job, write down the current scheduler settings, make a minor change to the schedule, save the change, edit the job again and restore the previous settings, then save it again, This will make the ownership change propagated to Quartz tables. Since the third party scheduler Quartz that JasperReports Server uses has its own table to track the job user information in its trigger table as a BLOB type, you will not able to make that change using a SQL query update. This step is mandatory if original job owner is removed from repository (user is deleted from jiuser table). Otherwise, we will get "owner not found" error when job is triggered to run a report.
Please also make sure new job owner has the write permission to repository folder that receives report job output.
Please login as the new job owner and test run report in the foreground to make sure report indeed runs successfully without any access error to report resources (such as ad hoc view, topic, domain, data source, input control, resource properties, etc).

To find all report jobs for the new job owner, you can use the following query to list report units and their job ids:

    select r.report_unit_uri as report_uri,
           r.id as job_id 
      from jireportjob r
inner join jiuser   u on u.id = r.owner    and u.username = '{to_user}'
inner join jitenant t on t.id = u.tenantid and t.tenantid = '{to_user_organization}'

TTC-20150307-58532-20170427-20180227