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

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: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

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: 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.

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: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 INSTANCE is replaced with the actual name of an instance of SQL Server.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<INSTANCE>

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQLServer

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows Messaging Subsystem

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\MSSQLServer

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\SQLCTRS

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\SQLServer

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSDTC

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQL$<INSTANCE>

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SNMP\Parameters\ExtensionAgents

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLServer

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlset\Services\SQLAgent$<INSTANCE>

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation

HKEY_CURRENT_USER\Software\Microsoft\Mail HKEY_CURRENT_USER\Control Panel\International

Xp_regwrite allowed paths

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

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

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<INSTANCE>

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlset\Services\SQLAgent$<INSTANCE>

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Replication

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: