Permissions for SQL Server Agent & Activity Monitor

_{Wednesday, February 6, 2008 10:16 AM}

I am currently setting up various access groups for SQL Server Management Studio and am struggling to find the permission scripts that will alllow me to give people access to the Activity Monitor (under the Management folder) and also to the SQL Server Agent (showing all of the scheduled josb etc).  The problem is that as soon as I remove permissions to certain actions, the users can no longer see these folders on the left hand pane.

Any clues???

_{Tuesday, August 25, 2009 10:42 PM ✅Answered | 2 votes}

I found this post while searching for information on this.  To update this post for future reference, the Active Monitor tool requires VIEW SERVER STATE permission; this permission is required to select from DMVs such as sys.sysprocesses.  Open a connection (i.e. using sqlcmd or Management Studio) using your sysadmin credentials and issue the following statement:

GRANT VIEW SERVER STATE TO << login name >>

Or you can do it through SSMS.

1. Right Click on the SQL Login in SSMS under Security -> Logins and click Properties.
2. Click on Securables.
3. Click Add
4. Select the last Radio Button that should be "The server 'Servername\Instance' "
5. Click Ok.
6. The bottom box will be populated Explicit permissions for ServerName\Instance
7. Scroll to the bottom of this list.
8. Check the box for Grant for View Server State Permission.

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

_{Wednesday, February 6, 2008 9:27 AM | 1 vote}

I am currently setting up various access groups for SQL Server Management Studio and am struggling to find the permission scripts that will alllow me to give people access to the Activity Monitor (under the Management folder) and also to the SQL Server Agent (showing all of the scheduled josb etc).  The problem is that as soon as I remove permissions to certain actions, the users can no longer see these folders on the left hand pane.

Any clues???