Can't find a permission set to run sp_whoisactive on an Azure DB

[proofrock]

Hi, so sorry to come up with a permission issue, but this is a big pain point to which we couldn't find a solution yet, despite involving Microsoft support.

We have a number of Azure SQL databases, and we installed sp_whoisactive on them. As long as it's called by the db owner all is well. But if I create an user, assign roles/grants to it, and call the stored procedure with it, it fails.

More precisely, we tried this:

CREATE ROLE nightly_checks;
ALTER ROLE db_datareader ADD MEMBER nightly_checks;
ALTER ROLE db_datawriter ADD MEMBER nightly_checks;
ALTER ROLE db_ddladmin ADD MEMBER nightly_checks;
GRANT SHOWPLAN TO nightly_checks;
GRANT VIEW DATABASE STATE TO nightly_checks;
GRANT VIEW DATABASE PERFORMANCE STATE TO nightly_checks;
GRANT EXECUTE TO nightly_checks;

CREATE USER nightly_checks_app WITH PASSWORD = '...';
ALTER ROLE nightly_checks ADD MEMBER nightly_checks_app;

Of course something is overkill, here, but this is everything we tried. Then, executing exec sp_whoisactive from (e.g.) python gives:

pymssql._pymssql.DatabaseError: (297, b'The user does not have permission to perform this action.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n')

This is python, but every client we tried gives the same result, even management studio.

Could you help us, or spot something that we did wrong?

Best regards,

 Germano
 

PS: the documentation cites VIEW SERVER STATE as a permission that is needed, but it cannot be granted on an azure db.