Hello!

Unlike certain objects like tables, views etc. in SSDT projects, windows logins can be more dificult 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.

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.

sqlcmdvars_properties

publish_profile

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!

run_deploy

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.

renameSqlCmdVar

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

objectsadded

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