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
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.

2 Responses to Minimal permissions needed to run sp_WhoIsActive

  1. Pingback: BPOTW 2015-08-21 | SQL Notes From The Underground

  2. Thank you for this. You saved me a lot of time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: