Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Trying to view properties of the Database Expand / Collapse
Author
Message
Posted Thursday, October 11, 2012 9:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 20, 2014 9:23 AM
Points: 82, Visits: 665
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
Post #1371579
Posted Thursday, October 11, 2012 9:22 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 4:25 AM
Points: 918, Visits: 2,507
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
Post #1371584
Posted Thursday, October 11, 2012 9:35 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 20, 2014 9:23 AM
Points: 82, Visits: 665
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
Post #1371601
Posted Friday, October 12, 2012 4:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128

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
Post #1372012
Posted Friday, October 12, 2012 9:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 20, 2014 9:23 AM
Points: 82, Visits: 665
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
Post #1372204
Posted Friday, October 12, 2012 4:18 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 20, 2014 9:23 AM
Points: 82, Visits: 665
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?
Post #1372381
Posted Friday, October 12, 2012 4:50 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 40,385, Visits: 36,828
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 2008, MVP
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

Post #1372383
Posted Friday, October 12, 2012 5:02 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 20, 2014 9:23 AM
Points: 82, Visits: 665
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)

Post #1372386
Posted Saturday, October 13, 2012 5:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 40,385, Visits: 36,828
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 2008, MVP
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

Post #1372413
Posted Saturday, October 13, 2012 9:05 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 20, 2014 9:23 AM
Points: 82, Visits: 665
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
Post #1372481
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse