Differences between xp_instance_RegRead and xp_RegRead

Question

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

Answer 1

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.

Answer 2

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