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— Dan Thompson (@LeedsDBA) August 12, 2018
Anyone have any links to good resources?
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.