Skip to content

Instantly share code, notes, and snippets.

@danielgreen
Created May 30, 2013 12:23
Show Gist options
  • Save danielgreen/5677460 to your computer and use it in GitHub Desktop.
Save danielgreen/5677460 to your computer and use it in GitHub Desktop.
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…
/* 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
/* 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)]
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
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. */
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment