April 13, 2010 at 11:33 pm
I want to give access to some users to view the job activity monitor. Now I know there is the new MSDB roles, SQLAgentUserRole, SQLAgentReaderRole etc in SQL Server 2005 but these don't fit the bill because typically the owner of all jobs is sa so I can't use SQLAgentUserRole and I don't want the user be able to do anything other than READ from the Job Activity Monitor so I can't use SQLAgentReaderRole - so how can I do it or can it even be done?
Thanks
April 15, 2010 at 2:09 pm
If owner of all the jobs is sa then sqlagentreader role suits your scenario.
October 16, 2012 at 4:45 pm
This forum thread answered my question about giving access to usrs, but it didn't tell how to give that access to the users. To do this:
Open the instance in SSMS.
Open the "Security" folder and then the "Logins" folder.
Right-Click on the user name and choose properties. If the user is not listed then you will have to add a "New Login".
Click on the "User Mappings" option.
Check the box next to the msdb database under "Users mapped to this login".
Add the user login name under "User" and add the "Default Schema" (eg: dbo).
Check the box next to the appropriate role under "Database role membership for: msdb".
Click "OK" and the user will have that permission for the Job Activity Monitor.
October 17, 2012 at 6:39 am
Use the following to grant VIEW SERVER STATE to a user. With this permission, they can see Activity Monitor. Please research this permission to make sure it does not give other permissions that you do not wish the user to have.
USE master
GO
GRANT VIEW SERVER STATE TO [<domain>\<username>]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply