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 your 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:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\ExtendedProcedures
For a named instance of SQL Server 2000, locate the following subkey in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance_name>\MSSQLServer\ExtendedProcedure
Note Instance_name
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 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\SQLCTRSHKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\SQLServer
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSDTCHKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQL$<INSTANCE>
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SNMP\Parameters\ExtensionAgentsHKEY_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\InternationalXp_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