Why is sql script not valid when referenced by post deploy

Question

I have a Script.PostDeployment.sql which references DataFactoryRights.sql

ScriptPostDeployment.sql, build action PostDeploy:

:r .\DataFactoryRights.sql

DataFactoryRights.sql, build action None:

CREATE USER [dataFactory] FROM EXTERNAL PROVIDER;

When I try to build, I get the error:

Severity Code Description Project File Line Suppression State Error SQL72007: The syntax check failed 'Incorrect syntax near FROM.' in the batch near: 'CREATE USER [dataFactory] FROM EXTERNAL PROVIDER;'

If I comment out like --:r .\DataFactoryRights.sql, it builds ok

Answer 1

The error is caused by the T-SQL action:

CREATE USER [dataFactory] FROM EXTERNAL PROVIDER;

Data Factory is service principals.

Microsoft MSFT confirmed that:

"We currently don't allow service principals to create users in SQL DB. We will update the documentation when this feature is available. Thanks!"

That's why you comment out like --:r .\DataFactoryRights.sql, it builds ok.

Ref:https://github.com/MicrosoftDocs/sql-docs/issues/2323#issuecomment-595417579

Hope this helps.