Microsoft KB Archive/907739

= You cannot modify a SQL Server Agent job for an instance of SQL Server 2000 in SQL Server Management Studio when you connect to the instance by using an account that is not a member of the SQL Server sysadmin fixed server role =

Article ID: 907739

Article Last Modified on 1/31/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Standard Edition

-



Bug #: 401713 (SQLBUDT)



SYMPTOMS
In Microsoft SQL Server 2005, when you create or modify a SQL Server Agent job for an instance of SQL Server 2000, you may receive the following error message:

Alter failed for Job ' '. (Microsoft.SqlServer.Smo)

--

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

--

EXECUTE permission denied on object 'sp_help_operator', database 'msdb', owner 'dbo'. (Microsoft SQL Server, Error: 229)

The problem occurs when all the following conditions are true:
 * You use SQL Server Management Studio or SQL Server Management Objects (SMO) to connect to an instance of Microsoft SQL Server 2000.
 * The account that you use to connect to the instance of SQL Server 2000 is not a member of the SQL Server sysadmin fixed server role.

Note When you try to create a new SQL Server Agent job, the SQL Server Agent job will be created even though you receive the error message. However, you cannot modify the newly created SQL Server Agent job by using the non-administrator account.



CAUSE
This problem occurs because you do not have sufficient user rights to access the sp_help_operator stored procedure.

When you create or modify a SQL Server Agent job, the SQL Server Management Studio or the SMO calls the sp_help_operator stored procedure to retrieve the properties of the newly created SQL Server Agent job.

However, if you connect to the instance by using an account that is not a member of the sysadmin fixed server role, you cannot access the stored procedure in SQL Server 2000.



WORKAROUND
To work around this problem, you must connect to an instance of SQL Server 2000 by using an account that is a member of the SQL Server sysadmin fixed server role in SQL Server 2000.

If you must use the account that is not a member of the SQL Server sysadmin fixed server role, you can manage the SQL Server Agent job by using SQL Server Enterprise Manager.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

Keywords: kbsql2005connect kbbug KB907739

-

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

© Microsoft Corporation. All rights reserved.