Knowledge Base

BUG: DOC: DB_OWNER Roles Cannot Administer All Jobs

Article ID: 201021

Article Last Modified on 2/24/2004

Microsoft SQL Server 7.0 Standard Edition
Microsoft SQL Server 2000 Standard Edition

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:

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

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'
						
```
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.

Additional query words: Doc err docerr

Keywords: kbbug kbpending KB201021