Minimal permissions needed to run sp_WhoIsActive

If you’re running sp_WhoIsActive on your SQL Server instance (like I do on all my instances), it might be useful to make this tool available for your colleagues. They can use it to determine the workload on the server, or to see if the query they are running is blocking other processes.

This was the exact question I got this morning. One of the members of our BI team wanted to have permissions on sp_WhoIsActive, and I was struggling (for the thousandth time) to remember the minimal permissions I needed to give him. So I’m going to be a smart guy for once, and document it here. And hopefully this might help you as well (and save you the time I lost on it).

Permissions on the object
To test this, I’ve created a new login (TestLogin) with just read permissions on a single database. If you try to execute sp_WhoIsActive with those permissions, you’ll see this error message:

To fix that, go to the master database, add the login, and add execute permissions on the sp_WhoIsActive stored procedure:

You could also script this out like this:

USE master
GRANT EXECUTE ON dbo.sp_WhoIsActive TO TestLogin

Now that that’s fixed, you still can’t execute sp_WhoIsActive:

To fix that, you need to right-click on the instance name, go to properties -> Permissions, and add “View server state” permissions for the login:

Again, you can also do this via T-SQL:

USE master

If you execute sp_WhoIsActive now, it works without any issues.