SQL Server xp_regread access denied

I have a client that is having an 'Access Denied' to a call to xp_regread (trying to find the database path), but I cannot seem to reproduce this. I have tried running the following

REVOKE execute on xp_regread to public

But it still works. I also found some articles on SQL Server 2000 problems (http://support.microsoft.com/kb/887165) but the clients server is 2005. Any ideas on how to go开发者_如何学Go about fixing this problem.

Edit: I have tried the following

USE MASTER
GO

REVOKE execute on xp_regread to public
GO

DECLARE @InstanceName nvarchar( 128 )
SET @InstanceName = ISNULL( CONVERT( nvarchar( 128 ), SERVERPROPERTY( 'InstanceName' ) ), N'MSSQLSERVER' )
DECLARE @InstanceKey nvarchar( 128 )
EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\', @InstanceName, @InstanceKey OUTPUT

print @InstanceName
print @InstanceKey

But it is still working. To be clear, I am trying to reproduce an issue where the call does NOT work. Revoking permissions was one way that I read should do it, but it still works for me.

It's an extended stored procedure so needs

EXEC master..xp_regread ...

Then, it would be

USE master
GO
GRANT EXECUTE ON xp_regread TO public
GO

If an explicit DENY has been set then REVOKE will remove it. This is not the same as GRANT though. This only applies to a normal user.

Anyone with sysadmin rights or db_owner in master (sa is dbo in master) will bypass permissions anyway

You should explicitly DENY the permission:

GRANT DENY ON xp_regread TO XYuser.

Be careful though, it might cause issues later. Like the backup dialog in SSMS won't work for that person.

To reproduce the issue, first you have to make a difference between two different errors: 'Access is denied.' and 'EXECUTE permission was denied'.

The first error is due to a configuration issue over a non sysadmin user. The second is due to lack of execute permission over the extended procedure.

For both please follow the post How to configure permissions for xp_regread