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?

score 2 · Accepted Answer · answered Oct 18 '17 at 15:21

2

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.

answered Oct 18 '17 at 15:21

Alberto Morillo

13,893
2
24
30

3

I wonder nobody mentioned [SQLCMD Variables](https://msdn.microsoft.com/en-us/library/bb386162(v=vs.100).aspx), which is the solution I ended up with – Liero Oct 23 '17 at 09:03

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

1 Answers1