SQL Server Performance Forum – Threads Archive

Minimum permissions to view SQL Agent jobs

I am attempting to create a user login that can view all the jobs of the SQL Agent, including the Job History and Job Properties, using the Enterprise Manager interface.
I would prefer not to give this user more permissions or a stronger Server Role than is necessary.
What are the minimum permissions or Server Role I can assign to this user in order to achieve this ?

Its not possible to view jobs in SQLAgent without a privileged SYSADMIN. You wouldn’t be able to set up something like this for viewing job schedules and job histories through Enterprise Manager. The user would need to own the jobs or be a sysadmin and in either case, the user could change the jobs. The other alternative is to write a stored procedure that retrieves the information and grant the appropriate permissions for this. BOL refers:
When sp_help_job is invoked by a user who is a member of the sysadmin fixed server role, sp_help_job will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, sp_help_job will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, sp_help_job will fail. This is true only for MicrosoftÂ® Windows NTÂ® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and sp_help_job is always executed under the security context of the Windows 9.x user who started SQL Server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>