[SOLVED] Use SQLCMD Variables for User Name in DACPAC

Use SQLCMD Variables for User Name in DACPAC

I am trying automate the creation of users in a Synapse Dedicated SQL Pool. Building and integrating a DACPAC in an Azure DevOps pipeline works as described in the Microsoft Documentation. The problem is, that i want to specify the username at build-time of the DACPAC as using an SQLCMD variable as follows:

CREATE USER [$(TestUserName)] FROM EXTERNAL PROVIDER;

This throws the following build error:

SQL70604: SqlCmd variable reference is not allowed in object names ($(TestUserName)).

Am I taking a completely wrong approach to the problem, or does anybody know how to get this working?

Solution

I hit exactly the same problem and moved some of the security to post-deployment scripts. You lose some of the benefits of the users being in the main project but it solves the problem. You also have to write defensive scripts IF EXISTS ...

An example, using sqlcmd variables which are passed in in the YAML pipeline using the /v argument:

IF NOT EXISTS ( SELECT * FROM sys.database_principals WHERE name = 'someUser-$(envShort)-developers' )
    CREATE USER [someUser-$(envShort)-developers] FOR EXTERNAL PROVIDER;

Role membership sp_addrolemember was also used to post-deployment scripts for the same reason