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.

SQL SERVER - Invalid Object Name 'master.dbo.spt_values' in Management Studio spt-values-err-01

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

Solarwinds

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 ….

SQL SERVER - Invalid Object Name 'master.dbo.spt_values' in Management Studio spt-values-err-02

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

SQL SERVER - Invalid Object Name 'master.dbo.spt_values' in Management Studio spt-values-err-03

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)

Solarwinds
, , , , ,
Previous Post
SQL SERVER – Interesting Observation – Count of Temporary Table Re-generations
Next Post
SQL SERVER – How to Identify Columns Used In A View?

Related Posts

Leave a Reply

Menu