BUG: DOC: DB_OWNER Roles Cannot Administer All Jobs

ID: Q201021

The information in this article applies to:

Microsoft SQL Server version 7.0

BUG #: 52033 (SQLBUG_70)

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 this to be a problem in SQL Server version 7.0.

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 :
Version : winnt:7.0
Platform : winnt
Issue type : kbbug