I’m using the
Microsoft.SqlServer.Management.Smo.Scripter library from within a PowerShell script to pull SQL object creation scripts from a DB as part of an automated process. The user account used for the script has minimum permissions; it is
VIEW ANY DATABASE and
VIEW ANY DEFINITION, and the user mapped to each DB can explicitly
sys.sql_expression_dependencies. Using this permission set, I can use the account to generate schema-only scripts from within SSMS.
However, when using the below option set for the
$scriptr.Options.AppendToFile = $False $scriptr.Options.AllowSystemObjects = $False $scriptr.Options.ClusteredIndexes = $True $scriptr.Options.NonClusteredIndexes = $True $scriptr.Options.DriAll = $True $scriptr.Options.ScriptDrops = $False $scriptr.Options.IncludeHeaders = $False $scriptr.Options.ToFileOnly = $True $scriptr.Options.Indexes = $True $scriptr.Options.Permissions = $True $scriptr.Options.WithDependencies = $False $scriptr.Options.Triggers = $True $scriptr.Options.ScriptData = $False $scriptr.Options.EnforceScriptingOptions = $True $scriptr.Options.ContinueScriptingOnError = $True
… the script fails on every table, with the error
The SELECT permission was denied on the object 'theTable', database 'theDB', schema 'dbo'. Notice I’m explicitly not scripting table data, hence the confusion.
I profiled the scripting process, and found that the error occurs on the following SQL statement executed by
use (theDB) SELECT (CASE WHEN (tbl.is_memory_optimized=0) THEN ISNULL((SELECT SUM (spart.rows) FROM sys.partitions spart WHERE spart.object_id = tbl.object_id AND spart.index_id < 2), 0) ELSE ISNULL((SELECT COUNT(*) FROM (dbo).(theTable)), 0) END) AS (RowCount)FROM sys.tables tbl WHERE SCHEMA_NAME(tbl.schema_id)=N'dbo' AND tbl.name=N'theTable'
I direct your attention to the
SELECT COUNT(*). Without SELECT permissions on the DB for the user, this will obviously fail.
I need to stop the scripter object from trying to run the above command. Granting blanket
SELECT access to every DB on the server is a Very Bad Thing™, but it’s what would need to happen in order for this SELECT COUNT(*) to work. I don’t need a rowcount, I don’t know why the scripter would need one, and at this point I don’t really care, I just need it to not happen so I can fix this process. There has to be an option I’m missing.
PS: this script works fine as written on two Azure-hosted DB servers with identical account permissions. The Azure DBs are v14 (2016) and I also have a legacy v10 (2008 R2) server that it runs against without error; the failure occurs on a v15 (2019) database server.