Trying to view properties of the Database

Question

Trying to view properties of the Database

D-SQL

SSCrazy

Points: 2265
More actions
October 11, 2012 at 9:20 am

#263186

When I right click on the database to view properties on any of the databases on the server I get this error
Invalid object name 'master.dbo.spt_values'. (.Net SqlClient Data Provider)
This is a back up of another Database. I can select the tables and do normal functions. As soon as I right click and select properties that error pops up.
Thanks is advance

Viewing 15 posts - 1 through 15 (of 15 total)

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

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 1

Either you dont have access to the Master db or more likley someone has done a clean up after mistakenly creating objects in the Master db and deleted the table along with the other objects.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

D-SQL SSCrazy Points: 2265 More actions · Answer 2

I verified the the permissions and we are SA also I looked at teh tables from the one database and the one having the issues and cant see any difference is there a script to find the missing objects out there that you know of?

Thanks is advance

OTF SSCertifiable Points: 6104 More actions · Answer 3

This blog post might help you:

http://blogs.msdn.com/b/suhde/archive/2009/04/05/invalid-object-name-master-dbo-spt-values-microsoft-sql-server-error-208.aspx

D-SQL SSCrazy Points: 2265 More actions · Answer 4

Thanks, I found that and tried running the u scripts I get the error doesnt exist or does not have permissions. We have SA rights I cant find that table or view any where even in the database its copied from . I get that error when right clicking to view properties.

Thanks for your help

D-SQL SSCrazy Points: 2265 More actions · Answer 5

I figured out the issue here Im missing a row in

[master].[INFORMATION_SCHEMA].[TABLE_PRIVILEGES]

GRANTOR GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE

dbo public master dbo spt_values SELECT NO

What is the recommended way to add this row in this view?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

If you're sa, it won't be a permissions issue, sa has all permissions without needing any specific grants.

Is the table there (select name from master.sys.tables)?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

D-SQL SSCrazy Points: 2265 More actions · Answer 7

no its missing the values in the system views in the master database, I have been trying to add these manually but I can not gettin this error Ad hov updates to system catalogs are not allowed. I turned on sp_configure 'Ad Hoc Distributed Queries' but still unable to add. Thanks in advance

GRANTORGRANTEETABLE_CATALOGTABLE_SCHEMATABLE_NAMEPRIVILEGE_TYPEIS_GRANTABLE

dbo public masterdbo spt_valuesSELECT NO

SELECT TOP 1000 [GRANTOR]

,[GRANTEE]

,[TABLE_CATALOG]

,[TABLE_SCHEMA]

,[TABLE_NAME]

,[PRIVILEGE_TYPE]

,[IS_GRANTABLE]

FROM [master].[INFORMATION_SCHEMA].[TABLE_PRIVILEGES]

with out these values in the view we are unbale to select the database properties

gettting error Invalid object name 'master.dbo.spt_values'. (Microsoft SQL Server, Error:208)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

You cannot edit the system tables in SQL 2008 (besides, those aren't the system tables, they're views)

Try

GRANT SELECT on spt_values TO public

However, the lack of that permission will not be the problem. I just tested revoking that permission to public and with it removed, as a sysadmin, I can still see all the database properties, because a sysadmin (sa or any member of the sysadmin group) has all permissions on the server implicitly and does not need any specific granted permissions.

Does that table exist in master?

Ad-hoc distributed queries is for OPENROWSET, nothing to do with system tables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

D-SQL SSCrazy Points: 2265 More actions · Answer 9

Im trying to add a row to this view [INFORMATION_SCHEMA].[TABLE_PRIVILEGES]

Thats where Im getting the error ists in the master system Views

we are missing this row

dbopublicmasterdbospt_valuesSELECTNO

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

You cannot insert a row to a system table (and those views are views of the system tables).

The grant statement I posted grants the permission that you say you are missing which will have the effect of getting that row added to the underlying tables and hence having the row you want appear in the view.

It likely won't fix the root problem, but it'll result in the row you want being present.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

D-SQL SSCrazy Points: 2265 More actions · Answer 11

I tried to run this on the server in question

GRANT SELECT on spt_values TO public

but recieved an error

Msg 15151, Level 16, State 1, Line 1

Cannot find the object 'spt_values', because it does not exist or you do not have permission.

This is the same error I also get if I use the u scripts this is why I was trying to add the rows manually

Thanks for your help and if you have any mre suggestions please let me know, I guess we can restore the master DB but that was our last resort.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

'k, so it is as I suspected (and mentioned several posts ago), the spt_values table is not there. Someone must have dropped it at some point for whatever reason.

You will need to either restore a backup of master that does have that table or you will need to rebuild the system databases then recreate logins, linked servers, jobs, etc.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

OTF SSCertifiable Points: 6104 More actions · Answer 13

D-SQL (10/15/2012)
I tried to run this on the server in question
GRANT SELECT on spt_values TO public
but recieved an error
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'spt_values', because it does not exist or you do not have permission.
This is the same error I also get if I use the u scripts this is why I was trying to add the rows manually
Thanks for your help and if you have any mre suggestions please let me know, I guess we can restore the master DB but that was our last resort.

I thought the u_tables.sql script rebuilt the spt_values table?

You might want to ensure you have the right permissions and then try again.

I suggest you backup the database as it is before you start tinkering with it.

D-SQL SSCrazy Points: 2265 More actions · Answer 14

This will be my first time retoring a master db as I have restored many just not the master, are there steps for this as I have tried and keeps erroring out and states needs to be in single user mode which also says master DB can not be in single user mode.

Now after the failure I can not right click to view the properties back to the same issue I was facing on the other server.

Thanks in advance