xp_regRead Cheat Sheet

Xp_regread is an undocumented stored procedure. It enables you to read the literal registry path that you specify via T-SQL.


EXEC [master].[dbo].[xp_regread] @rootkey='HKEY_LOCAL_MACHINE',
@key='Software\Microsoft\MSSQLServer\Setup',
@value_name='SQLPath'



You can use the OUTPUT parameter with xp_regread to return the value back.



declare @outputValue nvarchar (128)
EXEC [master].[dbo].[xp_regread] @rootkey='HKEY_LOCAL_MACHINE',
@key='Software\Microsoft\MSSQLServer\Setup',
@value_name='SQLPath',
@value = @outputValue OUTPUT
SELECT @outputValue

You can check who has access to xp_regread by running the following query;


SELECT
OBJECT_NAME(major_id) AS [Extended Stored Procedure],
USER_NAME(grantee_principal_id) AS [User]
FROM
sys.database_permissions
WHERE
OBJECT_NAME(major_ID) ='xp_regread'

As of SQL Server 2012 installations it appears that the public role gets the permission by default.

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s