Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    1

    Unanswered: Viewing SQL Server Agent Jobs

    Hello,

    All our SQL Server Agent Jobs are owned by sa. When there is an application problem we would like our developers to be able to log in to SQL Server under a troubleshooting account and be able to view the success (or failure) of these jobs.

    Is there anyway that we can enable an account for them that would enable them to do this without making that account sa? Or am I wasting my time in trying?

    Thanks,
    Gary.

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Re: Viewing SQL Server Agent Jobs

    RE:
    Hello, All our SQL Server Agent Jobs are owned by sa.
    Q1 When there is an application problem we would like our developers to be able to log in to SQL Server under a troubleshooting account and be able to view the success (or failure) of these jobs.

    Q2 Is there anyway that we can enable an account for them that would enable them to do this without making that account sa? Or am I wasting my time in trying?

    Thanks,
    Gary.
    A1 A typical implementation involves creating an sa equivalent account(standard and / or integrated) for each individual needing the rights sysadmin group membership provides e.g.(saMaryDeveloper, saTomDeveloper, saDickDeveloper, saHarryDeveloper).

    A2 Sysadmin group membership confers that capability, (but that is likely what was meant by "making that account sa"?). One could implement a scheme where some jobs run under a proxy account context (instead of sa); but that is a different matter / situation.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245

    Re: Viewing SQL Server Agent Jobs

    There are perhaps some other alternatives, depending on how much work you want to put in to the effort.

    You can write queries against sysjobs, sysjobsteps and sysjobhistory to view job performance. You can use sp_help_job to get a lot of information about the current status of a job (but no job history).

    I think DBA's solution is less work because the users can have the functionality of EM in a "restricted" environment. This alternative requires you to write some front-end code to display the information and it may be less satisfactory; still it is an alternative you might consider.

    I use something similar for my Level 1 support admins at a remote site to be able to see the status of all jobs that are scheduled. They can't do anything (restart a job), but they can immediately identify if a job failure is the likely cause of a problem.

    As someone else pointed out on this forum, there is always more than one way to skin a cat.


    Regards,

    Hugh Scott

    Originally posted by DBA
    A1 A typical implementation involves creating an sa equivalent account(standard and / or integrated) for each individual needing the rights sysadmin group membership provides e.g.(saMaryDeveloper, saTomDeveloper, saDickDeveloper, saHarryDeveloper).

    A2 Sysadmin group membership confers that capability, (but that is likely what was meant by "making that account sa"?). One could implement a scheme where some jobs run under a proxy account context (instead of sa); but that is a different matter / situation.

  4. #4
    Join Date
    Jan 2003
    Posts
    3

    Try this

    #1 The job shouldn't have broke in prod in the first place.

    #2 And they should have a mirror stage environment that they could use to diagnose the problem from .


    To answer your question from MSDB - give your dev group permission to run these sprocs. It's all they need if you can't handle the above.

    sp_get_composite_job_info
    sp_help_jobhistory @job_id = [jobId from above], @mode = N'SEM'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •