9

I'm getting the following error when trying to read a SQL Job.

The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'

How can I fix this?

gbn
  • 422,506
  • 82
  • 585
  • 676
Curtis
  • 101,612
  • 66
  • 270
  • 352

3 Answers3

12

You need to GRANT the SELECT permission for the user of your job.

A simple GRANT.

USE pubs
GO

GRANT SELECT
ON authors
TO public
GO 

Further information about GRANT

Neil Knight
  • 47,437
  • 25
  • 129
  • 188
  • I don't know much about whether GRANT is needed for msdb, but I ran this script, changing 'authors' to the objects which required permission, and now all is working fine. cheers! – Curtis Jun 16 '11 at 12:34
6

You use the "SQL Server Agent Fixed Database Roles" in msdb (MSDN link): don't assigned permission directly, it'll be a pain to manage

And a "how to" for SSMS: "How to: Configure a User to Create and Manage SQL Server Agent Jobs (SQL Server Management Studio)"

Note: you need a user (MSDN) in one of these roles: no need to GRANT any rights

gbn
  • 422,506
  • 82
  • 585
  • 676
0

Make sure you aren't overriding the default user, usually a SQL Agent Account, with a user who does not belong to the necessary role. Leave this box blank unless you need to run as a specific user, and ensure that user has all needed role memberships.

Joey Morgan
  • 823
  • 6
  • 9