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.

[erikdarlingdata]

@proofrock this is a pretty old issue and it hasn’t been updated in a while, so I’m going to close it out. Hopefully you got it sorted.

[proofrock]

As there was no answer, we couldn't sort it. In the end we used other tools. Is this tool abandoned?

[erikdarlingdata]

@proofrock no, it just doesn’t get frequent updates. But what you ran into was a permissions issue that likely had a lot of local factors involved and not something that would require a fix to the procedure.

[proofrock]

On the other hand, what we found is that basically to run the tool you'll have to be in the admin group. No possibility otherwise. This should be referenced in the documentation, as it's not something relevant to my installation only, it's perfectly reproducible for any Azure installation.

[erikdarlingdata]

@proofrock sure, submit a documentation change PR where you think it would make sense. I don’t use SQLDB personally.