We had the same issue and never could find any combination that satisfied our needs.

We had to separate the use cases in 2 different workspaces as mention here:

https://docs.microsoft.com/en-us/azure/cloud-adoption-framework/scenarios/data-management/best-pract...

The "analytical" workspace still have the issue that users "could" drop tables since only credential passthrough can be used and no security is on the hive. However, at least it doesn't interfere with job processes.

Also, being in 2 different workspaces, we now required a job (launch by an Azure Devops Pipeline) to create those "hive table" in the analytical workspace as well (Obviously, Not ideal). We didn't want to bother with an "external" hive.

We were also told that the unity catalog could "potentially" fix this (didn't seem 100% sure). However, there is no release date as far as I know.