BUG: DOC: DB_OWNER Roles Cannot Administer All Jobs (201021)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q201021
BUG #: 52033 (SQLBUG_70)
BUG #: 52033 (Shiloh)

SYMPTOMS

The SQL Server 7.0 Books Online incorrectly states "Only members of the sysadmin fixed server role or the db_owner fixed database role can execute sp_delete_jobs." This should actually read "Execute permissions default to the public role, but only the job owner or a member of the sysadmin fixed server role can delete a job." This also applies to other job stored procedures, such as sp_help_job.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

To reproduce this problem, perform the following steps:
  1. Start Query Analyzer and execute the following query:

    execute msdb..sp_add_job @job_name = 'test job',
   @enabled = 1,
   @description = 'test job',
   @owner_login_name = 'sa',
   @notify_level_eventlog = 2,
   @notify_level_email = 2,
   @notify_level_netsend =2,
   @notify_level_page = 2
  2. Connect again using a different user account that is assigned the db_owner role and execute the following query:

    msdb..sp_delete_job @job_name='test job'
  3. The following error message will be returned:
    Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 61
    The specified @job_name ('test job') does not exist.
Modification Type:MajorLast Reviewed:2/24/2004
Keywords:kbBug kbpending KB201021
©2004 Microsoft Corporation. All rights reserved.