Use SQL variables in DACPAC deployment to differentiate permissions

Question

I have a SQL database on Azure and deploy any schema changes to it using SqlPackage.exe, using a .dacpac file. I am looking to modify this such that I can use a sql variable to control certain changes in the schema, based on the environment I am deploying to. So in .sqlproj file, I added the following:

  <ItemGroup>
    <SqlCmdVariable Include="DEPLOYMENTTYPE">
      <DefaultValue>
      </DefaultValue>
      <Value>$(SqlCmdVar__1)</Value>
    </SqlCmdVariable>
  </ItemGroup>

Then in the .sql files for setting up Security, I added conditions like the following:

CREATE ROLE [db_newprodrole]
    AUTHORIZATION [dbo];

GO
IF ($(DEPLOYMENTTYPE) = 'production')
    ALTER ROLE [db_newprodrole] ADD MEMBER [newestprodmember];

However, I am getting a syntax error in this IF condition:

SQL46010: Incorrect syntax near (.

Could you please help? How can I set up permissions on the database to be conditional on the new variable I introduced?

Thank you!

Answer 1

For string substitution, the variable needs to be wrapped in quotes:

IF ('$(DEPLOYMENTTYPE)' = 'production')
    ALTER ROLE [db_newprodrole] ADD MEMBER [newestprodmember];