Create user script with variable domain user login

Question

We have a DACPAC that has a create user script. The user has a log in that needs to be set to a windows domain user When we do a build for Test/Staging/Release we need to be able to apply a different domain and user for the users log in.

I thought we might be able to use SQLCMD variables but I just get a SQL71501 Error when trying to use this That script looks something like this:

CREATE USER [Username]
    For Login [$(SQLLoginDomain)]
    WITH DEFAULT_SCHEMA = [SCHEMANAME]
GO

Answer 1

My advice based on bitter experience is to keep anything that is environment specific out of your SQL Server Database Project. Rather, apply anything that is environment specific (permisins etc) as a separate T-SQL script after the DACPAC has been deployed. If you are doing this with Release Management I have a soup-to-nuts blog series that includes publishing DACPACs and separate permissions scripts here.The post about applying a tokenised permissions script is here.

Answer 2

We ended up solving this by using a post deployment script in the dacpac and as long as you do a check before trying to call the user to see if they already exist it all works as that is the only place you can use them bar the pre deployment script.