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 public, can VIEW ANY DATABASE and VIEW ANY DEFINITION, and the user mapped to each DB can explicitly SELECT from 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 Scripter class:

    $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 Scripter:

use [theDB]
(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.

  • 1
    I tried to recreate your problem with SQL Server 2019 CU9 from the info in your question without success. Please add a Minimal, Complete, and Verifiable Example to your question along with the specific SQL Server and SMO versions.
    – Dan Guzman
    Apr 7 '21 at 11:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.