Azure SQL Database system view/function permissions

Question

I have a diagnostic query that uses these system views/functions:

• sys.dm_exec_cursors
• sys.dm_exec_sql_text
• sys.sysprocesses
• sys.syslockinfo

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?

Answer 1

I would replace sys.sysprocesses with sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests per the Mapping System Tables to System Views reference, since sys.sysprocesses is deprecated, and may be removed at some future point. I would replace sys.syslockinfo with sys.dm_tran_locks for the same reason.

The permissions required in Azure SQL Database for sys.dm_exec_connections is:

On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account or the Azure Active Directory admin account is required. On all other SQL Database service objectives, the VIEW DATABASE STATE permission is required in the database.

So, you could create a user that has VIEW DATABASE STATE permission, and probably run your diagnostic query successfully, assuming you modify it to use the correct system dynamic management views.

Microsoft's Monitoring with DMVs doc's page has further details around the required permissions, and what is visible.

Just FYI, Azure Monitor SQL Insights provides a fully functioning monitoring solution for Azure SQL Database, including the ability to query the monitor logs.