Error when scripting SQL View dbo.spt_values

Question

I am using a Powershell script to generate scripts of my SQL database objects (Tables, Views, StoredProcedures etc.). It was working fine in testing and still works on 2 out of the 3 servers being included. Recently the one server began returning this error:

Error: Exception calling "Script" with "1" argument(s): "The View '[dbo].[spt_values]' cannot be scripted as its data is not accessible.".

The Powershell script

$date_ = (date -f yyyyMMdd)
$ServerName = "ServerA" #If you have a named instance, you should put the name. 
$path = "\\core.windows.net\home\Rep\SQL"+"$date_"
 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
$IncludeTypes = @("Tables","StoredProcedures","Views","UserDefinedFunctions", "Triggers") #object you want do backup. 
$ExcludeSchemas = @("sys","Information_Schema")
$so = new-object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions')
 
$dbs=$serverInstance.Databases #you can change this variable for a query for filter yours databases.
foreach ($db in $dbs)
{
 $dbname = "$db".replace("[","").replace("]","")
 $dbpath = "$path"+ "\"+"$dbname" + "\"
 if ( !(Test-Path $dbpath))
 {$null=new-item -type directory -name "$dbname"-path "$path"}
 
 foreach ($Type in $IncludeTypes)
 {
 $objpath = "$dbpath" + "$Type" + "\"
 if ( !(Test-Path $objpath))
 {$null=new-item -type directory -name "$Type"-path "$dbpath"}
 foreach ($objs in $db.$Type)
 {
 if ($ExcludeSchemas -notcontains $objs.Schema ) 
 {
 $ObjName = "$objs".replace("[","").replace("]","") 
 $OutFile = "$objpath" + "$ObjName" + ".sql"
 $objs.Script($so)+"GO" | out-file $OutFile
 }
 }
 } 
}

The View causing the error is dbo.spt_values. It's in System Views in the Master database. The same View is on my other two servers and does not cause an error. I checked the Properties and cannot find a difference between the servers that work and the one that does not. I tried restarting SQL services to see if maybe something was blocking access to it, but there was no improvement. The Powershell is being executed from a Powershell component in an SSIS package.

• J.D.: Are you able to run SELECT * FROM dbo.spt_values on the problematic server?
• Don: I am. I compared the row counts to a 'good' server and the row counts are the same on both servers.
• Dan Guzman: Are you scripting the master database? On the servers where the error does not occur, is the view actually scripted out? Does this query return 1 on all 3 servers? USE mastere;SELECT OBJECTPROPERTY(OBJECT_ID('dbo.spt_values'), 'IsMSShipped');
• Martin Smith: Why do you need to script this anyway?
• Martin Smith: This is an ms shipped object so you don't need to script it out yourself as source code

Answer 1

If none of the attempts workout, may be you could try drop and re-create the view and see if that is going to resolve.

#Script to drop and recreate the view spt_values
$sql = @"
 USE master
 GO
 DROP VIEW IF EXISTS [dbo].[spt_values]
 GO
 CREATE VIEW dbo.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
 "@
 #Make a DAC connection to the problematic instance.
 $c = Connect-DbaInstance -SqlInstance 'YourSqlInstanceName' -DedicatedAdminConnection
 #Execute the script 
 $c | Invoke-DbaQuery -Query $sql
 #Close the connection
 $c.ConnectionContext.Disconnect()

• Don: @ S.D. - tried the drop and create w/ GRANT SELECT as suggested, but same result. Also tried after executing the Marksystemobject with same result.
• S.D.: Interesting, that data access error you are getting makes me think of checking/comparing the permissions on the account is being used for scripting. Does the account has same level of permissions on successful instance vs unsuccessful instance?
• Don: I think you are correct. I've looked through the permissions on SQL server and they appear the same. However, I deployed the job to the actual server and it runs fine on the server itself. I did notice an additional authentication prompt for this server that does not occur on the others. I'm thinking it is a network access issue. But I have resolved by running the job on the server itself.

Answer 2

I believe it is a network related issue with permissions. Have not been able to identify the root cause, but resolved by deploying and running from the server itself. I've recently bumped into another issue on this server specifically that requires me to elevate permissions when trying to map to a network drive.