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 ….
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.
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
- Start SQL Server in single user mode, which would need downtime.
- 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)