none
check for SQL Agent job without querying sysjobs_view

    Question

  • I want to check for the existence of a SQL 2000 Agent job, currently I do this using the code below. However, this fails when run as a non-admin user as it cannot query the view msdb.dbo.sysjobs_view. Is tehre another way to check for the agent job existence other than "if (agent.Jobs.Contains("abc"))" ?


       if (agent.Jobs.Contains("abc"))
                {
                    agent.Jobs["abc"].Start();
                }
                else
                {
                Job j = new Job(agent, "abc");
                j.Create();
    Friday, September 04, 2009 1:51 PM

Answers

  • Hi

    In order to view the jobs of the agent, the user must be assigned the specific permissions. The SQLAgentReaderRole is a database role located in the msdb database to acheive it.

    Add the user to this role and execute the same piece of code.

    -Sreekar
    • Marked as answer by KJian_ Saturday, September 12, 2009 7:04 AM
    Sunday, September 06, 2009 2:17 PM
  • Hi Firmbyte,

    Yes there is no SQLAgentReaderRole in SQL Server 2000. We can add the user to TargetServersRole role in MSDB database.

    For more information, please refer to the following link:
    http://www.sql-server-performance.com/faq/sqlagent_scheduled_jobs_p1.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by KJian_ Saturday, September 12, 2009 7:04 AM
    Wednesday, September 09, 2009 11:34 AM

All replies

  • Hi

    In order to view the jobs of the agent, the user must be assigned the specific permissions. The SQLAgentReaderRole is a database role located in the msdb database to acheive it.

    Add the user to this role and execute the same piece of code.

    -Sreekar
    • Marked as answer by KJian_ Saturday, September 12, 2009 7:04 AM
    Sunday, September 06, 2009 2:17 PM
  • Hi

    In order to view the jobs of the agent, the user must be assigned the specific permissions. The SQLAgentReaderRole is a database role located in the msdb database to acheive it.

    Add the user to this role and execute the same piece of code.

    -Sreekar

    ...it's SQL 2000, no SQLAgentReaderRole
    Sunday, September 06, 2009 2:31 PM
  • Hi Firmbyte,

    Yes there is no SQLAgentReaderRole in SQL Server 2000. We can add the user to TargetServersRole role in MSDB database.

    For more information, please refer to the following link:
    http://www.sql-server-performance.com/faq/sqlagent_scheduled_jobs_p1.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by KJian_ Saturday, September 12, 2009 7:04 AM
    Wednesday, September 09, 2009 11:34 AM