Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trying to view properties of the Database


Trying to view properties of the Database

Author
Message
D-SQL
D-SQL
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 754
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
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
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
D-SQL
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 754
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
OTF
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 4128
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
D-SQL
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 754
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
D-SQL
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 754
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?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47198 Visits: 44367
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
D-SQL
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 754
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

GRANTOR GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
dbo public master dbo spt_values SELECT 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)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47198 Visits: 44367
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
D-SQL
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 754
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

dbo public master dbo spt_values SELECT NO

Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search