Permissions in SQL Server 2005 to allow users to view the Management Activity monitor?

For SQL Server 2000 we have a user login mapped to msdb with database role membership of db_datareader and public checked. This seems to allow the developers to view the Management Activity monitor. For SQL Server 2005 the same mapping is in place but the developers cannot view the Management Activity monitor. Developers are NOT granted the sysadmin role, and should not have that role.

What permissions need to be set for SQL Server 2005 to allow users to view the Management Activity monitor? They should not be allowed to take actions on the activities.

The Active Monitor tool requires VIEW SERVER STATE permission; this permission is required to select from DMVs such as sys.sysprocesses.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thank you so much! This did the trick.

Barb

|||

Does Management Studio Express support the Activity Monitor?

I've enabled VIEW SERVER STATE for admin and still can't find the Activity Monitor in the GUI.

Thanks

|||

Unfortunately I am not familiar with the different GUI tools available for SQL Express. I would recommend asking this question on the SQL Express forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=385&SiteID=1) instead.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Where do you set this: To view the Activity Monitor the user needs VIEW SERVER STATE permission on a SQL Server 2005 server.

|||

The easiest way is to just open a connection (i.e. using sqlcmd or Management Studio) using your sysadmin credentials and issue the following statement:

GRANTVIEWSERVER STATETO <<login name>>

This will grant <<login_name>> VIEW ANY STATE permission.

-Raul Garcia

SDE/T

SQL Server Engine