Minimal permissions needed to run sp_WhoIsActive
August 14, 2015 2 Comments
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 GO GRANT EXECUTE ON dbo.sp_WhoIsActive TO TestLogin GO
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 GO GRANT VIEW SERVER STATE TO TestLogin GO
If you execute sp_WhoIsActive now, it works without any issues.