22

Whats the difference between XP_REGREAD and XP_INSTANCE_REGREAD. How to use them, is there any docs on these?

Gabriel Guimarães
  • 2,724
  • 3
  • 27
  • 41

2 Answers2

33

xp_regread reads the literal registry path that you specify. xp_instance_regread "converts" the path you specify so that it matches the instance of SQL Server that you're currently using.

As an example, I have Developer and Express editions of SQL Server installed on my machine. I connect to the Express instance and run these two commands:

declare @dir nvarchar(4000) 

exec master.dbo.xp_regread 
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\Setup',
    N'SQLPath',
    @dir output
select @dir

exec master.dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\Setup',
    N'SQLPath', 
    @dir output
select @dir

The first returns C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL which is the value found at the location I specified but it is not the correct installation path for my Express instance.

The second returns c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL which is the correct path for my Express instance. This value was actually read from HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.1\Setup which is quite different from the path I supplied to xp_instance_regread.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • I understood, thanks, I was getting mad at this, I then added admin rights to the account running SQL to have this right. Is there a way to read the registry without admin rights? – Gabriel Guimarães Dec 14 '10 at 15:06
  • @Gabriel: You should be able to grant execute permission on the procedure. – Joe Stefanelli Dec 14 '10 at 15:11
  • But Do I need to have my SQL Service account have admin rights on the machine? – Gabriel Guimarães Dec 14 '10 at 15:12
  • So long as you don't manually modify the windows service's launch settings to use a different account, the windows service will have read/write permissions on the instance registry keys. Probably these permissions are configured when the keys are first created (i.e., during the initial configuration/install of SQL server). Note that in many cases (including configuration changes made via SSMS), the values of these keys are ignored by SQL server until SQL Server is restarted. – Brian Jun 16 '20 at 16:17
3

Gabriel, The Service account does not have to be dbo. As long as the user is a user in the master database, you can grant execute permissions on system objects in the master db. 

GRANT EXEC ON OBJECT::master.dbo.xp_instance_regread TO ServiceAccountUserName
brian
  • 3,635
  • 15
  • 17
  • 2
    I was having a problem that I was trying to read the registry with a nvarchar(max) and it doesn't work like that, only with a number-sized char variable. – Gabriel Guimarães Dec 14 '10 at 16:53