SQL SERVER – Invalid Object Name ‘master.dbo.spt_values’ in Management Studio

One of my clients contacted me for On Demand (55 minutes) as they believed that this was a simple issue. John was honest enough to confess the mistake which he has done, which lead to error related to invalid object name.

John was trying to troubleshoot a deadlock issue, and he found that this specific server doesn’t have a system_health session in extended events.

So, he found that the definition of the session is defined in U_tables.sql file from “Install” folder.

C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER2014\MSSQL\Install

He executed the script, but it failed with below the messages.

This file creates all the system tables in master.
drop view spt_values ….
Creating view ‘spt_values’.
Msg 208, Level 16, State 1, Procedure spt_values, Line 56
Invalid object name ‘sys.spt_values’.
sp_MS_marksystemobject: Invalid object name ‘spt_values’
Msg 15151, Level 16, State 1, Line 61
Cannot find the object ‘spt_values’, because it does not exist or you do not have permission.
drop table spt_monitor ….
Creating ‘spt_monitor’.
Grant Select on spt_monitor
Insert into spt_monitor ….

Now, there was a bigger problem. A lot of places in SSMS, he started seeing below errors.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Invalid object name ‘master.dbo.spt_values’. (Microsoft SQL Server, Error: 208)

John knew that he should create the view ‘master.dbo.spt_values’ using below but was unable to.

WORKAROUND/SOLUTION

To create master.dbo.spt_values the reference is needed to sys.spt_values. This can’t be accessed by normal connection. There are two ways

1. Start SQL Server in single user mode, which would need downtime.
2. Connect using Dedicate Administrator Connection (DAC). You can read more about DAC over here. Diagnostic Connection for Database Administrators

After making connection run below script.

create view spt_values as
select name collate database_default as name,
	number,
	type collate database_default as type,
	low, high, status
from sys.spt_values
go
EXEC sp_MS_marksystemobject 'spt_values'
go
grant select on spt_values to public
go

Reference: Pinal Dave (https://blog.sqlauthority.com)