How to set SQL user password when deploying database project using CI/CD pipeline

Question

I have database project (.sqlproj) in Visual Studio where I create user like this:

CREATE USER [MyUser] WITHOUT LOGIN; --I dont want to store password in source control
GO
ALTER ROLE [db_datareader] ADD MEMBER [MyUser]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [MyUser]
GO
ALTER ROLE [db_executor] ADD MEMBER [MyUser]

I build dacpack in VSTS Build and deploy it to Azure in VSTS Release:

How do I setup the continuous deployment so that it will create user with password?

Answer 1

This plug-in in Azure Marketplace maybe useful.

You can also create logins using PowerShell and include conditional execution.

More alternatives explained here.

Hope this helps.