Microsoft KB Archive/201021

= BUG: DOC: DB_OWNER Roles Cannot Administer All Jobs =

Article ID: 201021

Article Last Modified on 2/24/2004

-

APPLIES TO


 * 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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.