SSDT With Different User Accounts Per Environment

29. December 2014 09:20

 

I really like SSDT (SQL Server Data Tools). It makes it a breeze to add all of the database code into source control and it generates update scripts to publish changes with ease. One problem I’ve run into is using different user accounts in different environments. We have different domains for development and production so the accounts in the environments are different. Here is an overview of one way to handle this situation.

First I added a security folder with three files to the SSDT project.

image

The Users_DEFAULT.sql should have the build action set to “Build” while the Users_DEV.sql and Users.PRODUCTION.sql should have the build action set to “None”.

Users_DEFAULT.sql can be empty to start while the DEV and PRODUCTION files should contain the necessary TSQL to create the user(s).

-- server login
CREATE LOGIN [csd\wbsmmservice] FROM WINDOWS
GO
	
-- DB login
CREATE USER [wbsmmservice]
	FOR LOGIN [csd\wbsmmservice]     WITH DEFAULT_SCHEMA = [db_owner];
GO
 
 
-- role membership
EXEC  sp_addrolemember @rolename='db_datareader', @membername='wbsmmservice'
GO
EXEC  sp_addrolemember @rolename='db_datawriter', @membername='wbsmmservice'
GO

 

The magic happens during build events. I unloaded the project (right click and unload) and then edited it to add the following.

  <Target Name="BeforeBuild">     <Message Text="Copy files task running for configuration: $(Configuration)" Importance="high" />     <Copy Condition=" '$(Configuration)' == 'PRODUCTION' " SourceFiles="$(ProjectDir)Security\Users_PRODUCTION.sql" DestinationFiles="$(ProjectDir)Security\Users_DEFAULT.sql" OverwriteReadOnlyFiles="true" />     <Copy Condition=" '$(Configuration)' == 'Debug' " SourceFiles="$(ProjectDir)Security\Users_DEV.sql" DestinationFiles="$(ProjectDir)Security\Users_DEFAULT.sql" OverwriteReadOnlyFiles="true" />   </Target>

 

So, now before the build, based on the project/solution configuration, either the DEV or PRODUCTION users file will be copied over the DEFAULT file which will then be used in the compile.

 

About the author

I'm a .NET developer, a husband and a father of three beautiful girls.

Month List