Hello!

Unlike certain objects like tables, views etc. in SSDT projects, windows logins can be more difficult to deploy, mainly because the logins will most likely be across different domains. In one place where I used to work they implemented a solution of having many different configurations in the Configuration Manager, and a pre build task would switch in and out certain files to be built. This is quite a messy way to do things, because you have to build the same project relative to the number of environments you have.

And so the reason I bring up this subject is that yesterday on Twitter there was a question about how to deploy Logins/Users etc using SSDT.

Is it possible to store a windows based USER in a DB solution in SSDT? Or should you just use DB Roles and then use some hocus pocus to map everything up on post deploy? #SQLHelp #DevOps #SSDT
Anyone have any links to good resources?

— Dan Thompson (@LeedsDBA) August 12, 2018

The solution I have used lately is to have the domain as a sqlcmdvar in the SSDT project, and set that at deploy time via the publish profile. I have created a repo called Sundae, available on GitHub to demonstrate.

I’ve added the permissions in the post-deploy script. I guess you could add it as part of the project, but for this demo I’ve added it all to one script. This is in the Script.PostDeployment1 file.

IF NOT EXISTS (
		SELECT name
		FROM master.sys.server_principals
		WHERE name LIKE '$(domain)\bzzzt'
		)
BEGIN
	CREATE LOGIN [$(domain)\bzzzt]
	FROM WINDOWS WITH DEFAULT_DATABASE = [Sundae]
		,DEFAULT_LANGUAGE = [us_english]
END

IF NOT EXISTS (
		SELECT name
		FROM [sys].[database_principals]
		WHERE [type] = 'U'
			AND name = N'bzzzt'
		)
BEGIN
	CREATE USER [bzzzt]
	FOR LOGIN [$(domain)\bzzzt]
	WITH DEFAULT_SCHEMA = [dbo]
END

IF DATABASE_PRINCIPAL_ID('bzzzt_role') IS NULL
BEGIN
	CREATE ROLE bzzzt_role AUTHORIZATION [dbo]

	GRANT SELECT
		,VIEW DEFINITION
		ON SCHEMA::dbo
		TO bzzzt_role

	ALTER ROLE bzzzt_role ADD MEMBER [bzzzt];
END

Alright, all very good, but how do we alter the value of the sqlcmdvar at deploy time through a pipeline? The DeploySundae file is how!

By using PoshSSDTBuildDeploy and by adding the switch getsqlcmdvars when executing Publish-DatabaseDeployment we can set the value of a sqlcmdvar by creating a PowerShell variable that is identically named as the sqlcmdvar.

And once I’ve deployed I can check that the login, user and role were all deployed.

The idea is that this “DeploySundae” script is checked in along with the code, then executed as a step and the arguments passed along. Making use of a PowerShell module to execute a lot of the functionality that is stored in Nuget/PowerShell Gallery means you can control what version you are using, whilst not having loads of code checked in locally.