Microsoft KB Archive/887165

= BUG: You may receive an &quot;Access is denied&quot; error message when a query calls an extended stored procedure after you upgrade from SQL Server 2000 to SQL Server 2000 Service Pack 4 =

Article ID: 887165

Article Last Modified on 11/16/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Service Pack 4

-



Important This article contains information about how to modify the registry. Make sure that you back up the registry before you modify it. Make sure that you know how to restore the registry if a problem occurs. For more information about how to back up, restore, and modify the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows registry



SYMPTOMS
After you upgrade a Microsoft SQL Server 2000-based computer to Microsoft SQL Server 2000 Service Pack 4 (SP4), a SQL Server query that calls a registry extended stored procedure may fail, and you receive the following error message:

Access is denied.

You receive the error message when you use any one of the registry extended stored procedures in the following list:
 * xp_regread
 * xp_instance_regread
 * xp_regenumkeys
 * xp_instance_regenumkeys
 * xp_regenumvalues
 * xp_instance_regenumvalues
 * xp_regwrite
 * xp_instance_regwrite
 * xp_regaddmultistring
 * xp_instance_regaddmultistring
 * xp_regremovemultistring
 * xp_instance_regremovemultistring
 * xp_regdeletevalue
 * xp_instance_regdeletevalue
 * xp_regdeletekey
 * xp_instance_regdeletekey



CAUSE
This problem occurs because SQL Server 2000 SP4 implements a restriction on which registry paths the registry extended stored procedures can access. When a query calls a registry extended stored procedure, the registry extended stored procedure examines the requested registry subkey against a control list of registry hives. If the registry subkey that the query requests is not in the control list, you receive the following error message:

'Access is denied.'

This error message has nothing to do with the permissions of the SQL Server user account in the context of which one of these registry extended stored procedures is called. The error message occurs even if the user has Administrator permissions.

Note When you receive the error message, the registry extended stored procedure returns no results.



RESOLUTION
Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall the operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

Warning The registry extended stored procedures are undocumented and are subject to change. Therefore, we do not recommend that you call these procedures in your client code. Future implementations of these procedures may not be supported. Additionally, there is no guarantee that past implementations will continue to be supported in later versions of SQL Server.

To resolve this problem, modify the registry key that controls access to the registry extended stored procedures. The control list is located in the registry under the MSSQLServer subkey. For a default instance of SQL Server 2000, locate the following subkey in the registry:

Note This registry key is not created when you apply SQL Server 2000 SP4 to a clustered instance of SQL Server.

For a named instance of SQL Server 2000, locate the following subkey in the registry:

Note  is the name of the SQL Server 2000 named instance.

In the ExtendedProcedure subkey, there are two REG_MULTI_SZ registry entries:
 * Xp_regread Allowed Paths
 * Xp_regwrite_Allowed Paths

The Xp_regread Allowed Paths entry contains the following list of allowed registry values for read-only registry extended stored procedures:
 * xp_regread
 * xp_instance_regread
 * xp_regenumkeys
 * xp_instance_regenumkeys
 * xp_regenumvalues
 * xp_instance_regenumvalues

The Xp_regwrite Allowed Paths entry contains the following list of allowed registry values for write registry extended stored procedures:
 * xp_regwrite
 * xp_instance_regwrite
 * xp_regaddmultistring
 * xp_instance_regaddmultistring
 * xp_regremovemultistring
 * xp_instance_regremovemultistring
 * xp_regdeletevalue
 * xp_instance_regdeletevalue
 * xp_regdeletekey
 * xp_instance_regdeletekey

To allow access to the registry subkey that you want, you can either delete the Xp_regread Allowed Paths entry or the Xp_regwrite_Allowed Paths entry, or delete both entries. Additionally, you can modify the values that are in the Xp_regread Allowed Paths entry or the Xp_regwrite_Allowed Paths entry and add the extended stored procedure that you want the query to access. If you delete both the Xp_regread Allowed Paths entry and the Xp_regwrite Allowed Paths entry, you disable the restriction for the registry values. Therefore, the registry extended stored procedure behavior reverts to the pre-SQL Server 2000 SP4 behavior. To achieve the same result, you can remove the ExtendedProcedures registry key. We recommend that you extend each list with valid registry values instead of disabling the restriction that SQL Server 2000 SP4 implements.

Xp_regread allowed paths
The following is a sample for the Xp_regread Allowed Paths entry.

Note  is replaced with the actual name of an instance of SQL Server.

Xp_regwrite allowed paths
The following is a sample for the Xp_regwrite Allowed Paths entry.

Note  is replaced with the actual name of an instance of SQL Server.



MORE INFORMATION
When you install SQL Server 2000 Service Pack 4, the installation program pre-populates both the Xp_regread Allowed Paths registry entry and the Xp_regwrite Allowed Paths registry entry with the extended stored procedures that SQL Server 2000 can access.

For more information about SQL Server 2000 service packs, visit the following Microsoft Web site:

http://technet.microsoft.com/en-us/sqlserver/bb331754.aspx

Additional query words: SQL2000 access denied

Keywords: kbinfo kbsqlserv2000sp4fea kbtshoot kbbug KB887165

-

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

© Microsoft Corporation. All rights reserved.