I have a diagnostic query that uses these system views/functions:
The actual query is pretty big but I can include it if it's needed.
I was hoping to run this in an automated way via a dedicated login and user, but I haven't been able to get it to work. From researching it, it seems like it may be impossible to do with anything other than the initial system admin account, due to how permissions work in Azure SQL Database. The database is currently using the Hyperscale tier.
I'm not able to grant system view permissions, though I was able to grant database view permissions to the new user. The user can run the query without errors, it just returns no data... Running the same query at the same time as the system admin account does return data.
Is this just impossible in my scenario?