xp_regread execute permission denied

Question

xp_regread execute permission denied

Chrissy321

SSCoach

Points: 15763
More actions
September 16, 2011 at 12:18 pm

#257539

Hello All,
I am having the issue described here:
https://connect.microsoft.com/SQLServer/feedback/details/515132/management-studio-generates-scripts-that-use-unsupported-undocumented-procedures
More specifically the user right-clicks on a table and gets 'EXECUTE permission denied on object xp_regread, database amaster, owner dbo'
The version of management studio is 2008 R2 SP1. The user is attempting to manage a 2000 SP4 database.
Can someone help me analyze what the proposed solution does?
When the poster says 'This is the code that needs to be modified to remove the need of xp_instance_regread' do they mean:
1) something in the clients SSMS install needs to be changed; or
2) something server side needs to be changed
If the former how do I do this?
If the latter it looks like the only thing the code does is
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT
Wouldn't xp_instance_regread only read from the registry? How does that accomplish anything?
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sqltung Hall of Fame Points: 3962 More actions · Answer 1

It looks like that command returns the location of your SQL Server installation, no matter the particular version you are using. But why you are getting this error when you right-click on a table... that is puzzling.

My impression is that the extended procedure is not missing, just not accessible from your connection.

Are you connecting, or have you tried connecting to that server with an administrator account?

sqltung Hall of Fame Points: 3962 More actions · Answer 2

Also, does this work from your login?

declare @SmoRoot nvarchar(100)

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT

select @SmoRoot

Chrissy321 SSCoach Points: 15763 More actions · Answer 3

The GUI works fine with an administrative account

Using the non-privileged account and running

declare @SmoRoot nvarchar(100)

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT

I get:

EXECUTE permission denied on object 'xp_instance_regread', database 'master', owner 'dbo'.

sqltung Hall of Fame Points: 3962 More actions · Answer 4

Chrissy,

Take a look at this link:

http://groups.google.com/group/microsoft.public.sqlserver.tools/browse_thread/thread/a7fb460d6f83b826

You may need to access the server with an admin account and enable EXECUTE permission on the procedure in the master db for the other user, or at least the public role.

Chrissy321 SSCoach Points: 15763 More actions · Answer 5

Thank you. I am looking at adding EXECUTE permissions for this user. My only concern is security issues. I believe permission on this procedure was removed when the 'lockdown' tool was run.

My determination is that the security risk of adding this permission back to the single user is minimal. If anyone else feels differently please educate me on how this could be a significant security risk.

kalathilayil Valued Member Points: 67 More actions · Answer 6

kalathilayil

Valued Member

Points: 67

July 25, 2012 at 11:22 pm

#1517737

It is a risk.Add sysadmin role to this user.

Federico Perez Grasshopper Points: 21 More actions · Answer 7

Instead of adding the sysadmin role to the user, you can configure the server with less permissions.

See https://sql.itrio.net/2013/09/04/how-to-configure-permissions-for-xp_regread-and-xp_instance_regread/