dbt-sqlserver: dbt run fails with "The specified schema name userid@domain either does not exist or you do not have permission to use it

Hello,

When I create a schema manually, add it to my profiles.yml, and then submit “dbt run”, my model is built successfully. However, if the schema specified in my profiles.yml does not exist, the build fails with:

"The specified schema name 'userid@domain' either does not exist or you do not have permission to use it" "CREATE SCHEMA failed due to previous errors" when active directory integrated authentication is used. This feels like a DB permissions issue but I am able to log into SSMS using active directory integrated and create schemas without issue. I am also not sure why my userid and domain are being concatenated for the schema name when my profiles.yml contains “schema: staging”.

I’m running dbt=0.19.2. What additional information can I provide for troubleshooting?

About this issue

Original URL
State: closed
Created 3 years ago
Reactions: 1
Comments: 15 (1 by maintainers)

Most upvoted comments

This happens because the user can not be created implicitly. If you configured your database like me, and provided permission to an Azure AD group (and not the user themselves), CREATE SCHEMA is not able to implicitly create the user.

It seems documented in the remarks section here.

w0ut0 on Sep 23, 2021

Edit: I saw your note to move it to tsql-utils. Is that functionality available in GitHub or do you just mean re-post there?

yes, I think so, but let’s have that discussion on the tsql-utls repo Issues page.

as for the main issue, now I’m rather stumped! if you DM me on the dbt Slack, I can give you test Azure SQL to play with (to see if the error persists).

Can you log into the db with SSMS, and execute CREATE SCHEMA new_schema without issue?

You might want to follow up with you cloud team’s set up because in my view, using “Active Directory - Integrated” with Azure SQL must mean that your cloud team has:

an on-premise Active Directory deployment,
either Azure AD Connect or Active Directory Federation Services enabled
someone who is the AAD admin of the Azure SQL server has already granted your identity the right permissions on the Azure SQL db

@alieus @NandanHegde15 any ideas what might be going on here?

dataders on Jul 21, 2021