Visual Studio 2012 Database projects (which are based on SQL Server Data Tools aka SSDT) allow the developer to define a database schema with tables, keys, indexes, stored procedures, etc. Out of the box, a Database project in VS 2012 does not provide an easy way to define upgrade scripts or environment-specific security (logins, users, roles, e…

Script.PostDeployment.sql

/* This script should have a Build Action of PostDeploy within the Visual Studio project. */

/*
Post-Deployment Script Template  						
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.		
 Use SQLCMD syntax to include a file in the post-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the post-deployment script.		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/

:r .\StaticData.sql	
:r .\Upgrade.sql
:r .\Security.sql

Security.sql

/* The SFR.TOR.Database project previously held scripts to create users and role memberships within
 * the Schema Objects folder, and permissions were defined in the Database.sqlpermissions file. However
 * these do not allow security to vary across environments. In particular, we need to use a different
 * login for Production than for UAT or Debug. To resolve this, all commands to create users, role memberships
 * and permissions are listed here. The commands refer to the $(DatabaseLogin) parameter which is defined in
 * each publish.xml file as a SqlCmdVariable. Since each project configuration (Debug, UAT, Production, etc) can 
 * specify its own publish.xml file, we are able to set DatabaseLogin to the relevant value for each environment. 
 * Additionally, if the project's deployment settings are configured to drop objects from the database that
 * do not appear in the project (i.e. within Schema Objects), then the users, role memberships and permissions
 * will always be dropped during the initial part of the deployment and then recreated by this script.
 * Please be aware of this, as an error in this script could render an application unable to access the database. */

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'$(DatabaseLogin)')
CREATE USER [$(DatabaseLogin)] FOR LOGIN [$(DatabaseLogin)]

EXECUTE sp_addrolemember @rolename = N'db_datawriter', @membername = N'$(DatabaseLogin)'
EXECUTE sp_addrolemember @rolename = N'db_datareader', @membername = N'$(DatabaseLogin)'

GRANT CONNECT TO [$(DatabaseLogin)]
GRANT EXECUTE TO [$(DatabaseLogin)]

StaticData.sql

DECLARE @DBVersionCount int

SET @DBVersionCount = (SELECT COUNT(*) FROM [dbo].[DatabaseVersion])

---- Only insert static data if no DB version record exists
---- When a new deployment is performed, the latest version of the schema will be deployed i.e. it won't deploy the initial version and run upgrades.
---- Therefore we must keep the statements in this script current, based on the latest database version, to ensure that static data is inserted ok.
IF @DBVersionCount = 0
BEGIN

  INSERT INTO [dbo].[DatabaseVersion] ([Version]) VALUES ('1.0')

	INSERT INTO [dbo].[Author] ([Name]) VALUES ('Stephen King')

	INSERT INTO [dbo].[Country] ([Name]) VALUES ('USA')
	
END

Upgrade.sql

DECLARE @CurrentVersion VARCHAR(10)

SET @CurrentVersion = (SELECT [Version] FROM DatabaseVersion)

IF @CurrentVersion = '1.0'
BEGIN
  -- Upgrade database to version 1.1
	
	UPDATE [dbo].[DatabaseVersion] SET [Version] = '1.1'
	
	INSERT INTO [dbo].[Author] ([Name]) VALUES ('Dan Brown')
	
	UPDATE [dbo].[Country] SET [Name] = 'United States of America' WHERE [Name] = 'USA'
END

--IF @CurrentVersion = '1.1'
--BEGIN
	-- Upgrade database to next version e.g. 1.2
--END

/* Code that should run during all upgrades regardless of version should be placed below this point. */