Exception when executing a query that requires data push-down to SQL Server#

Symptoms#

Query execution aborts with the following exception:

Microsoft.Data.SqlClient.SqlException (0x80131904): The specified schema name “dbo” either does not exist or you do not have permission to use it.

Schema name in the error message may differ.

Cause#

Insufficient privileges are granted to the user connecting to the SQL Server instance.

Additional context#

Querona uses a few strategies to push-down data, particularly to SQL Server. Data push-down requires a configuration on the SQL Server side that allows for data to be sent to the database engine. In case of lack of, or misconfiguration, an exception will be thrown during query execution that requires a data push-down.

Depending on the strategy configured for a particular connection, a Table-Valued Parameter (TVP) strategy stands out as the most efficient data push-down strategy for SQL Server and is a default.

Resolution#

For TVP strategy to work, the user in the source SQL Server has to have:

  1. The CREATE TYPE privilege. The following script grants the user login myuser the required privilege:

    GRANT CREATE TYPE TO [myuser] WITH GRANT OPTION;

  2. If you need to change the default schema for myuser, the below script changes it to schema [guest]:

    ALTER USER [myuser] WITH DEFAULT_SCHEMA=[guest];

  3. The ALTER SCHEMA privilege for user’s default schema:

    GRANT ALTER ON SCHEMA :: [guest] TO [myuser];