SSMS 2008 prompt

[sikander]

After installing SQL Server Management Studio 2008 I get the following
error alert whenever I right click on a table to get the table menu:

EXECUTE permission denied on object 'xp_instance_regread', database
'master', owner 'dbo'
http://sikander.org/temp/sqlmstudio.png

The server is remotely hosted and I log into to with a username/
password. This account has access to one user database only and does
not have access to the master database. I cannot modify permissions
for the user account.

This error did not show up in Management Studio 2005. Is there any way
to avoid getting this error every time I right click on a table ? Its
quite annoying and I hope there is a setting in the Management studio
that I can modify to avoid trying to executing xp_instance_regread

Thanks.

Management Studio version info:

Microsoft SQL Server Management Studio 10.0.1600.22 ((SQL_PreRelease).
080709-1414 )
Microsoft Data Access Components (MDAC) 2000.085.1132.00 (xpsp.
080413-0852)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 8.0.6001.18702
Microsoft .NET Framework 2.0.50727.3082
Operating System 5.1.2600

[Uri Dimant]

Hi
Are you running on Vista then you are likely encountering UAC but don't
have SQL configured to handle that

"sikander" <siq...@gmail.com> wrote in message
news:a6a304b3-678b-48f1...@c34g2000yqi.googlegroups.com...

[Erland Sommarskog]

sikander (siq...@gmail.com) writes:
> After installing SQL Server Management Studio 2008 I get the following
> error alert whenever I right click on a table to get the table menu:
>
> EXECUTE permission denied on object 'xp_instance_regread', database
> 'master', owner 'dbo'
> http://sikander.org/temp/sqlmstudio.png
>
> The server is remotely hosted and I log into to with a username/
> password. This account has access to one user database only and does
> not have access to the master database. I cannot modify permissions
> for the user account.

I checked on an SQL 2008 instance around the corner, and public has Execute
permission on xp_instance_regread. Seems like your host has performed a
lockdown and removed this permission. I think you have three options:

1) Convince your host that removing the permission on xp_instance_regread is
bad idea.
2) Downgrade to SSMS 2005.
3) Find a new hosting provider.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Aaron Bertrand [SQL Server MVP]]

> 1) Convince your host that removing the permission on xp_instance_regread is
> bad idea.

They could enable it and lock down the registry so that the service
account(s) only have access to the precise keys that Management Studio 2008
accesses.

FWIW, I do not have this issue at my shared hosting provider (OrcsWeb) nor
at any client sites.

[Erland Sommarskog]

For what it's worth, this is the batch that SSMS runs which calls
xp_instance_regread:

declare @RegPathParams sysname
declare @Arg sysname
declare @Param sysname
declare @MasterPath nvarchar(512)
declare @LogPath nvarchar(512)
declare @ErrorLogPath nvarchar(512)
declare @n int

select @n=0
select @RegPathParams=N'Software\Microsoft\MSSQLServer\MSSQLServer'+'\Parameters'
select @Param='dummy'
while(not @Param is null)
begin
select @Param=null
select @Arg='SqlArg'+convert(nvarchar,@n)

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', @RegPathParams, @Arg, @Param OUTPUT
if(@Param like '-d%')
begin
select @Param=substring(@Param, 3, 255)
select @MasterPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
end
else if(@Param like '-l%')
begin
select @Param=substring(@Param, 3, 255)
select @LogPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
end
else if(@Param like '-e%')
begin
select @Param=substring(@Param, 3, 255)
select @ErrorLogPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
end

select @n=@n+1
end

declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT

SELECT
CAST(case when 'a' <> 'A' then 1 else 0 end AS bit) AS [IsCaseSensitive],
CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit) AS [IsFullTextInstalled],
@LogPath AS [MasterDBLogPath],
@MasterPath AS [MasterDBPath],
@ErrorLogPath AS [ErrorLogPath],
@@MAX_PRECISION AS [MaxPrecision],
@SmoRoot AS [RootDirectory],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
CAST(SERVERPROPERTY(N'MachineName') AS sysname) AS [NetName],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
CAST(SERVERPROPERTY(N'ProductLevel') AS sysname) AS [ProductLevel],
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
CAST(SERVERPROPERTY('IsSingleUser') AS bit) AS [IsSingleUser],
CAST(SERVERPROPERTY('EngineEdition') AS int) AS [EngineEdition],
SERVERPROPERTY(N'ResourceVersion') AS [ResourceVersionString],
SERVERPROPERTY(N'ResourceLastUpdateDateTime') AS [ResourceLastUpdateDateTime],
SERVERPROPERTY(N'BuildClrVersion') AS [BuildClrVersionString],
SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
SERVERPROPERTY(N'CollationID') AS [CollationID],
SERVERPROPERTY(N'ComparisonStyle') AS [ComparisonStyle],
SERVERPROPERTY(N'SqlCharSet') AS [SqlCharSet],
SERVERPROPERTY(N'SqlCharSetName') AS [SqlCharSetName],
SERVERPROPERTY(N'SqlSortOrder') AS [SqlSortOrder],
SERVERPROPERTY(N'SqlSortOrderName') AS [SqlSortOrderName],
convert(sysname, serverproperty(N'collation')) AS [Collation]

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[sikander]

Nope, WinXP

Based on the other replies, I guess I have to ask the host to play
nice and update the permissions

[sikander]

On Aug 18, 9:41 am, "Aaron Bertrand [SQL Server MVP]"

Thanks all