What you are encountering is the expected behavior due to how Databricks isolates execution sessions and handles credential delegation between Jobs and SQL objects.

When you set a query to RunAsMode.VIEWER (Run as viewer), the query executes using the credentials of the Job's "Run as" service principal. Consequently, the resulting execution session (queryExecutionSessions) is owned entirely by that executing service principal. The CAN_MANAGE permission on the query object itself only grants you the ability to modify the query definition, it does not implicitly grant you access to another principal's execution session. Hence, you cannot view the output result.

With RunAsMode.OWNER (Run as owner), the query executes using your credentials, which grants you access to your own session outputs and limits the permissions to your personal access level rather than the service principal's.

You can follow below

If you want to simplify the permissions model and avoid cross-principal execution session errors entirely -You can unify the execution identity across the full cycle. Set the Service Principal as the Query Owner, configure the Query with RunAsMode.OWNER, and set the Job's "Run as" identity to that Service Principal. You can then manage data access directly via Unity Catalog. This ensures the Service Principal is always accessing its own execution sessions, and you act solely as the API caller triggering the job.

SQL File Task - You can switch the Job task type from a SQL Query task to a SQL File task. By storing the SQL statements directly in a file within the Workspace, you bypass the RunAsMode complexity completely. The SQL File task will strictly use the Job's "Run as" identity, streamlining the permission model.

More details here