Malformed view names in tests #585
Description
The Problem
- I was trying to run a basic test suite in a schema that's not the dbo schema
- I don't have permission to edit or create anything in the dbo schema
Here's the simple test file I used:
version: 2
models:
- name: dummy_hardcoded
description: "A dummy_test"
columns:
- name: column1
data_tests:
- unique
- not_nullAnd here's the resulting SQL it generated in the target/run directory:
- Create target schema if it does not
USE [my_connection];
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'my_schema')
BEGIN
EXEC('CREATE SCHEMA [my_schema]')
END
EXEC('create view
[my_schema.testview_9269]
as
select column1
from "my_connection"."my_schema"."dummy_hardcoded"
where column1 is null
;')
select
count(*) as failures,
case when count(*) != 0
then 'true' else 'false' end as should_warn,
case when count(*) != 0
then 'true' else 'false' end as should_error
from (
select * from
[my_schema.testview_9269]
) dbt_internal_test;
EXEC('drop view
[my_schema.testview_9269]
;')
Here's the error I get when I get to line 9:
Msg 2760, Level 16, State 1, Procedure my_schematestview_8091, Line 1
The specified schema name "dbo" either does not exist or you do not have permission to use it.
Total execution time: 00:00:00.080
The Solution
What's interesting is if I change line 9 from
EXEC('CREATE VIEW [my_schema.testview_9269] AS
to
EXEC('CREATE VIEW [my_schema].[testview_9269] AS
the SQL query is able to compile.
Applying this fix to the dbt-sqlserver project:
I believe this code (in dbt/include/sqlserver/macros/materializations/tests.sql):
[{{ target.schema }}.testview_{{ range(1300, 19000) | random }}]
Should change to this:
[{{ target.schema }}].[testview_{{ range(1300, 19000) | random }}]
Why this hasn't been discovered sooner/hasn't been a problem:
I think the reason why it has not come up before is that perhaps the users defined in the test suite have write privileges on the dbo schema so when the tests encounter the malformed schemas, they create them like so:
CREATE VIEW dbo.my_schematestview_9269
The only reason I noticed this is b/c I don't have access to the dbo schema on the database I'm working with! The docs say that in order for tests to work that
CREATE permissions above are required on the database level if you want to make use of tests or snapshots in dbt. You can work around this by creating the schemas used for testing and snapshots in advance and granting the right roles
But I was working in a schema where I had all the right roles! In my profiles.yml I even specified what schema I wanted to run in:
# Microsoft SQL Server
my_project:
target: dev
outputs:
dev:
type: sqlserver
driver: "ODBC Driver 18 for SQL Server"
server: "{{ env_var('SQL_SERVER') }}"
port: 1433
database: my_connection
schema: "my_schema"
user: "{{ env_var('SQL_SERVER_USER') }}"
pass: "{{ env_var('SQL_SERVER_PASSWORD') }}"The reason this didn't work is because of the malformed view name I mentioned earlier, and that it currently defaults to creating the view in the dbo schema.