Database script in project template now comes from the dbproj.

9 files changed, 699 insertions, 253 deletions

diff --git a/build.proj b/build.proj
index e887939..5567625 100644
--- a/build.proj
+++ b/build.proj
@@ -140,6 +140,9 @@
 	</Target>
 
 	<Target Name="BuildProjectTemplates">
+		<!-- Deploy the latest SQL script first, so that the class library can embed the latest version. -->
+		<MSBuild Projects="$(ProjectRoot)projecttemplates\RelyingPartyDatabase\RelyingPartyDatabase.dbproj" Targets="Deploy" />
+
 		<MSBuild Projects="@(ProjectTemplates)" />
 	</Target>
 
diff --git a/projecttemplates/RelyingPartyDatabase/Properties/Database.sqlcmdvars b/projecttemplates/RelyingPartyDatabase/Properties/Database.sqlcmdvars
index 38699ab..705af45 100644
--- a/projecttemplates/RelyingPartyDatabase/Properties/Database.sqlcmdvars
+++ b/projecttemplates/RelyingPartyDatabase/Properties/Database.sqlcmdvars
@@ -4,11 +4,7 @@
   <Properties>
     <Property>
       <PropertyName>Path1</PropertyName>
-      <PropertyValue>C:\Users\andarno\git\dotnetopenid\projecttemplates\WebFormsRelyingParty\App_Data\</PropertyValue>
-    </Property>
-    <Property>
-      <PropertyName>Path2</PropertyName>
-      <PropertyValue>C:\Users\andarno\git\dotnetopenid\projecttemplates\WebFormsRelyingParty\App_Data\</PropertyValue>
+      <PropertyValue>WEBROOT\App_Data\</PropertyValue>
     </Property>
   </Properties>
 </SqlCommandVariables>
\ No newline at end of file
diff --git a/projecttemplates/RelyingPartyDatabase/Properties/Database.sqldeployment b/projecttemplates/RelyingPartyDatabase/Properties/Database.sqldeployment
index 3856e8c..c3f58f1 100644
--- a/projecttemplates/RelyingPartyDatabase/Properties/Database.sqldeployment
+++ b/projecttemplates/RelyingPartyDatabase/Properties/Database.sqldeployment
@@ -2,8 +2,10 @@
 <DeploymentConfigurationSettings xmlns="urn:Microsoft.VisualStudio.Data.Schema.Project.DeploymentConfigurationSettings">
   <Version>1</Version>
   <Properties>
+    <AbortOnFirstError>False</AbortOnFirstError>
     <AlwaysCreateNewDatabase>False</AlwaysCreateNewDatabase>
     <BlockIncrementalDeploymentIfDataLoss>True</BlockIncrementalDeploymentIfDataLoss>
+    <CommentOutSetVarDeclarations>True</CommentOutSetVarDeclarations>
     <DeployDatabaseProperties>True</DeployDatabaseProperties>
     <DeploymentCollationPreference>UseSourceModelCollation</DeploymentCollationPreference>
     <DoNotUseAlterAssemblyStatementsToUpdateCLRTypes>False</DoNotUseAlterAssemblyStatementsToUpdateCLRTypes>
diff --git a/projecttemplates/RelyingPartyDatabase/RelyingPartyDatabase.dbproj b/projecttemplates/RelyingPartyDatabase/RelyingPartyDatabase.dbproj
index a11bba7..4ffaf9c 100644
--- a/projecttemplates/RelyingPartyDatabase/RelyingPartyDatabase.dbproj
+++ b/projecttemplates/RelyingPartyDatabase/RelyingPartyDatabase.dbproj
@@ -132,7 +132,7 @@
     <Build Include="Schema Objects\Database Level Objects\Service Broker\Routes\AutoCreatedLocal.route.sql">
       <SubType>Code</SubType>
     </Build>
-    <Build Include="Schema Objects\Database Level Objects\Storage\Files\C__Users_andarno_git_dotnetopenid_projecttemplates_WebFormsRelyingParty_App_Data_Database.mdf.sqlfile.sql">
+    <Build Include="Schema Objects\Database Level Objects\Storage\Files\Database.mdf.sqlfile.sql">
       <SubType>Code</SubType>
     </Build>
     <Build Include="Schema Objects\Database Level Objects\Storage\Files\Database_log.sqlfile.sql">
diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Database Level Objects/Storage/Files/C__Users_andarno_git_dotnetopenid_projecttemplates_WebFormsRelyingParty_App_Data_Database.mdf.sqlfile.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Database Level Objects/Storage/Files/C__Users_andarno_git_dotnetopenid_projecttemplates_WebFormsRelyingParty_App_Data_Database.mdf.sqlfile.sql
deleted file mode 100644
index 4854932..0000000
--- a/projecttemplates/RelyingPartyDatabase/Schema Objects/Database Level Objects/Storage/Files/C__Users_andarno_git_dotnetopenid_projecttemplates_WebFormsRelyingParty_App_Data_Database.mdf.sqlfile.sql
+++ /dev/null
@@ -1,3 +0,0 @@
-ALTER DATABASE [$(DatabaseName)]
-    ADD FILE (NAME = [C:\Users\andarno\git\dotnetopenid\projecttemplates\WebFormsRelyingParty\App_Data\Database.mdf], FILENAME = '$(Path2)Database.mdf', MAXSIZE = UNLIMITED, FILEGROWTH = 1024 KB) TO FILEGROUP [PRIMARY];
-
diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Database Level Objects/Storage/Files/Database.mdf.sqlfile.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Database Level Objects/Storage/Files/Database.mdf.sqlfile.sql
new file mode 100644
index 0000000..1df0e7a
--- /dev/null
+++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Database Level Objects/Storage/Files/Database.mdf.sqlfile.sql
@@ -0,0 +1,3 @@
+ALTER DATABASE [$(DatabaseName)]
+    ADD FILE (NAME = [$(Path1)Database.mdf], FILENAME = '$(Path1)Database.mdf', MAXSIZE = UNLIMITED, FILEGROWTH = 1024 KB) TO FILEGROUP [PRIMARY];
+
diff --git a/projecttemplates/RelyingPartyLogic/CreateDatabase.sql b/projecttemplates/RelyingPartyLogic/CreateDatabase.sql
index 0470448..2f8cfb0 100644
--- a/projecttemplates/RelyingPartyLogic/CreateDatabase.sql
+++ b/projecttemplates/RelyingPartyLogic/CreateDatabase.sql
@@ -1,213 +1,597 @@
-SET ANSI_NULLS ON
+/*
+Deployment script for RelyingPartyDatabase
+*/
+
 GO
-SET QUOTED_IDENTIFIER ON
+SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, NUMERIC_ROUNDABORT, QUOTED_IDENTIFIER OFF;
+
+
 GO
-CREATE PROCEDURE [dbo].[ClearExpiredNonces]
-AS
+/*
+:setvar Path1 "WEBROOT\App_Data\"
+:setvar DatabaseName "RelyingPartyDatabase"
+:setvar DefaultDataPath ""
+*/
 
-DELETE FROM dbo.[Nonce]
-WHERE [Expires] < getutcdate()
 GO
-SET ANSI_NULLS ON
+USE [master]
+
 GO
-SET QUOTED_IDENTIFIER ON
+IF (DB_ID(N'$(DatabaseName)') IS NOT NULL
+    AND DATABASEPROPERTYEX(N'$(DatabaseName)','Status') <> N'ONLINE')
+BEGIN
+    RAISERROR(N'The state of the target database, %s, is not set to ONLINE. To deploy to this database, its state must be set to ONLINE.', 16, 127,N'$(DatabaseName)') WITH NOWAIT
+    RETURN
+END
+
 GO
-CREATE PROCEDURE [dbo].[ClearExpiredAssociations]
-AS
+IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
+BEGIN
+    ALTER DATABASE [$(DatabaseName)]
+    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
+    DROP DATABASE [$(DatabaseName)];
+END
+
+GO
+PRINT N'Creating $(DatabaseName)...'
+GO
+CREATE DATABASE [$(DatabaseName)]
+    ON 
+    PRIMARY(NAME = [$(Path1)Database.mdf], FILENAME = '$(Path1)Database.mdf', MAXSIZE = UNLIMITED, FILEGROWTH = 1024 KB)
+    LOG ON (NAME = [Database_log], FILENAME = '$(Path1)Database_log.LDF', MAXSIZE = 2097152 MB, FILEGROWTH = 10 %) COLLATE SQL_Latin1_General_CP1_CI_AS
+GO
+EXECUTE sp_dbcmptlevel [$(DatabaseName)], 90;
+
+
+GO
+IF EXISTS (SELECT 1
+           FROM   [master].[dbo].[sysdatabases]
+           WHERE  [name] = N'$(DatabaseName)')
+    BEGIN
+        ALTER DATABASE [$(DatabaseName)]
+            SET ANSI_NULLS OFF,
+                ANSI_PADDING OFF,
+                ANSI_WARNINGS OFF,
+                ARITHABORT OFF,
+                CONCAT_NULL_YIELDS_NULL OFF,
+                NUMERIC_ROUNDABORT OFF,
+                QUOTED_IDENTIFIER OFF,
+                ANSI_NULL_DEFAULT OFF,
+                CURSOR_DEFAULT GLOBAL,
+                RECOVERY SIMPLE,
+                CURSOR_CLOSE_ON_COMMIT OFF,
+                AUTO_CREATE_STATISTICS ON,
+                AUTO_SHRINK OFF,
+                AUTO_UPDATE_STATISTICS ON,
+                RECURSIVE_TRIGGERS OFF 
+            WITH ROLLBACK IMMEDIATE;
+        ALTER DATABASE [$(DatabaseName)]
+            SET AUTO_CLOSE ON 
+            WITH ROLLBACK IMMEDIATE;
+    END
+
+
+GO
+IF EXISTS (SELECT 1
+           FROM   [master].[dbo].[sysdatabases]
+           WHERE  [name] = N'$(DatabaseName)')
+    BEGIN
+        ALTER DATABASE [$(DatabaseName)]
+            SET ALLOW_SNAPSHOT_ISOLATION OFF;
+    END
+
+
+GO
+IF EXISTS (SELECT 1
+           FROM   [master].[dbo].[sysdatabases]
+           WHERE  [name] = N'$(DatabaseName)')
+    BEGIN
+        ALTER DATABASE [$(DatabaseName)]
+            SET READ_COMMITTED_SNAPSHOT OFF;
+    END
+
+
+GO
+IF EXISTS (SELECT 1
+           FROM   [master].[dbo].[sysdatabases]
+           WHERE  [name] = N'$(DatabaseName)')
+    BEGIN
+        ALTER DATABASE [$(DatabaseName)]
+            SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
+                PAGE_VERIFY CHECKSUM,
+                DATE_CORRELATION_OPTIMIZATION OFF,
+                DISABLE_BROKER,
+                PARAMETERIZATION SIMPLE 
+            WITH ROLLBACK IMMEDIATE;
+    END
+
+
+GO
+IF IS_SRVROLEMEMBER(N'sysadmin') = 1
+    BEGIN
+        IF EXISTS (SELECT 1
+                   FROM   [master].[dbo].[sysdatabases]
+                   WHERE  [name] = N'$(DatabaseName)')
+            BEGIN
+                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
+    SET TRUSTWORTHY OFF,
+        DB_CHAINING OFF 
+    WITH ROLLBACK IMMEDIATE';
+            END
+    END
+ELSE
+    BEGIN
+        PRINT N'The database settings for DB_CHAINING or TRUSTWORTHY cannot be modified. You must be a SysAdmin to apply these settings.';
+    END
+
+
+GO
+IF EXISTS (SELECT 1
+           FROM   [master].[dbo].[sysdatabases]
+           WHERE  [name] = N'$(DatabaseName)')
+    BEGIN
+        ALTER DATABASE [$(DatabaseName)]
+            SET HONOR_BROKER_PRIORITY OFF 
+            WITH ROLLBACK IMMEDIATE;
+    END
+
+
+GO
+USE [$(DatabaseName)]
+
+GO
+IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
+    EXECUTE sp_fulltext_database 'enable';
+
+
+GO
+
+GO
+/*
+ Pre-Deployment Script Template							
+--------------------------------------------------------------------------------------
+ This file contains SQL statements that will be executed before the build script.	
+ Use SQLCMD syntax to include a file in the pre-deployment script.			
+ Example:      :r .\myfile.sql								
+ Use SQLCMD syntax to reference a variable in the pre-deployment script.		
+ Example:      :setvar TableName MyTable							
+               SELECT * FROM [$(TableName)]					
+--------------------------------------------------------------------------------------
+*/
+
+GO
+
+GO
+PRINT N'Creating dbo.AuthenticationToken...';
+
+
+GO
+SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
+
+
+GO
+CREATE TABLE [dbo].[AuthenticationToken] (
+    [AuthenticationTokenId]    INT            IDENTITY (1, 1) NOT NULL,
+    [UserId]                   INT            NOT NULL,
+    [OpenIdClaimedIdentifier]  NVARCHAR (250) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
+    [OpenIdFriendlyIdentifier] NVARCHAR (250) NULL,
+    [CreatedOn]                DATETIME       NOT NULL,
+    [LastUsed]                 DATETIME       NOT NULL,
+    [UsageCount]               INT            NOT NULL
+);
+
+
+GO
+SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
+
+
+GO
+PRINT N'Creating dbo.Consumer...';
+
+
+GO
+SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
+
+
+GO
+CREATE TABLE [dbo].[Consumer] (
+    [ConsumerId]             INT             IDENTITY (1, 1) NOT NULL,
+    [ConsumerKey]            NVARCHAR (255)  COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
+    [ConsumerSecret]         NVARCHAR (255)  COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
+    [X509Certificate]        IMAGE           NULL,
+    [Callback]               NVARCHAR (2048) NULL,
+    [VerificationCodeFormat] INT             NOT NULL,
+    [VerificationCodeLength] INT             NOT NULL,
+    [Name]                   NVARCHAR (50)   NULL
+);
+
+
+GO
+SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
+
+
+GO
+PRINT N'Creating dbo.IssuedToken...';
+
+
+GO
+SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
+
+
+GO
+CREATE TABLE [dbo].[IssuedToken] (
+    [IssuedTokenId]    INT             IDENTITY (1, 1) NOT NULL,
+    [ConsumerId]       INT             NOT NULL,
+    [UserId]           INT             NULL,
+    [Token]            NVARCHAR (255)  COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
+    [TokenSecret]      NVARCHAR (255)  COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
+    [CreatedOn]        DATETIME        NOT NULL,
+    [Callback]         NVARCHAR (2048) NULL,
+    [VerificationCode] NVARCHAR (255)  COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
+    [ConsumerVersion]  VARCHAR (10)    NULL,
+    [ExpirationDate]   DATETIME        NULL,
+    [IsAccessToken]    BIT             NOT NULL,
+    [Scope]            NVARCHAR (255)  NULL
+);
+
+
+GO
+SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
+
+
+GO
+PRINT N'Creating dbo.Nonce...';
+
+
+GO
+SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
+
+
+GO
+CREATE TABLE [dbo].[Nonce] (
+    [NonceId] INT           IDENTITY (1, 1) NOT NULL,
+    [Context] VARCHAR (255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
+    [Code]    VARCHAR (255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
+    [Issued]  DATETIME      NOT NULL,
+    [Expires] DATETIME      NOT NULL
+);
+
+
+GO
+SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
+
+
+GO
+PRINT N'Creating dbo.OpenIDAssociation...';
+
+
+GO
+SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
+
+
+GO
+CREATE TABLE [dbo].[OpenIDAssociation] (
+    [AssociationId]        INT           IDENTITY (1, 1) NOT NULL,
+    [DistinguishingFactor] VARCHAR (255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
+    [AssociationHandle]    VARCHAR (255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
+    [Expiration]           DATETIME      NOT NULL,
+    [PrivateData]          BINARY (64)   NOT NULL,
+    [PrivateDataLength]    INT           NOT NULL
+);
+
+
+GO
+SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
+
+
+GO
+PRINT N'Creating dbo.Role...';
+
+
+GO
+SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
+
+
+GO
+CREATE TABLE [dbo].[Role] (
+    [RoleId] INT           IDENTITY (1, 1) NOT NULL,
+    [Name]   NVARCHAR (50) NOT NULL
+);
+
+
+GO
+SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
+
+
+GO
+PRINT N'Creating dbo.User...';
+
+
+GO
+SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
+
+
+GO
+CREATE TABLE [dbo].[User] (
+    [UserId]               INT            IDENTITY (1, 1) NOT NULL,
+    [FirstName]            NVARCHAR (50)  NULL,
+    [LastName]             NVARCHAR (50)  NULL,
+    [EmailAddress]         NVARCHAR (100) NULL,
+    [EmailAddressVerified] BIT            NOT NULL,
+    [CreatedOn]            DATETIME       NOT NULL
+);
+
+
+GO
+SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
+
+
+GO
+PRINT N'Creating dbo.UserRole...';
+
+
+GO
+SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
+
+
+GO
+CREATE TABLE [dbo].[UserRole] (
+    [UserId] INT NOT NULL,
+    [RoleId] INT NOT NULL
+);
+
+
+GO
+SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
+
+
+GO
+PRINT N'Creating dbo.Consumer.IX_Consumer...';
+
+
+GO
+CREATE UNIQUE NONCLUSTERED INDEX [IX_Consumer]
+    ON [dbo].[Consumer]([ConsumerKey] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0);
+
+
+GO
+PRINT N'Creating dbo.IssuedToken.IX_IssuedToken...';
+
+
+GO
+CREATE UNIQUE NONCLUSTERED INDEX [IX_IssuedToken]
+    ON [dbo].[IssuedToken]([Token] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0);
+
+
+GO
+PRINT N'Creating dbo.Nonce.IX_Nonce_Code...';
+
+
+GO
+CREATE UNIQUE NONCLUSTERED INDEX [IX_Nonce_Code]
+    ON [dbo].[Nonce]([Context] ASC, [Code] ASC, [Issued] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0);
+
+
+GO
+PRINT N'Creating dbo.Nonce.IX_Nonce_Expires...';
+
+
+GO
+CREATE NONCLUSTERED INDEX [IX_Nonce_Expires]
+    ON [dbo].[Nonce]([Expires] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0);
+
+
+GO
+PRINT N'Creating dbo.OpenIDAssociation.IX_OpenIDAssociations...';
+
+
+GO
+CREATE UNIQUE NONCLUSTERED INDEX [IX_OpenIDAssociations]
+    ON [dbo].[OpenIDAssociation]([DistinguishingFactor] ASC, [AssociationHandle] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0);
+
 
-DELETE FROM dbo.OpenIDAssociation
-WHERE [Expiration] < getutcdate()
 GO
-SET ANSI_NULLS ON
-GO
-SET QUOTED_IDENTIFIER ON
-GO
-SET ANSI_PADDING ON
-GO
-CREATE TABLE [dbo].[Nonce](
-	[NonceId] [int] IDENTITY(1,1) NOT NULL,
-	[Context] [varchar](255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
-	[Code] [varchar](255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
-	[Issued] [datetime] NOT NULL,
-	[Expires] [datetime] NOT NULL,
- CONSTRAINT [PK_Nonce] PRIMARY KEY CLUSTERED 
-(
-	[NonceId] ASC
-)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
-) ON [PRIMARY]
-GO
-SET ANSI_PADDING OFF
-GO
-CREATE UNIQUE NONCLUSTERED INDEX [IX_Nonce_Code] ON [dbo].[Nonce] 
-(
-	[Context] ASC,
-	[Code] ASC,
-	[Issued] ASC
-)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
-GO
-CREATE NONCLUSTERED INDEX [IX_Nonce_Expires] ON [dbo].[Nonce] 
-(
-	[Expires] ASC
-)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
-GO
-SET ANSI_NULLS ON
-GO
-SET QUOTED_IDENTIFIER ON
-GO
-SET ANSI_PADDING ON
-GO
-CREATE TABLE [dbo].[OpenIDAssociation](
-	[AssociationId] [int] IDENTITY(1,1) NOT NULL,
-	[DistinguishingFactor] [varchar](255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
-	[AssociationHandle] [varchar](255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
-	[Expiration] [datetime] NOT NULL,
-	[PrivateData] [binary](64) NOT NULL,
-	[PrivateDataLength] [int] NOT NULL,
- CONSTRAINT [PK_OpenIDAssociations] PRIMARY KEY CLUSTERED 
-(
-	[AssociationId] ASC
-)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
-) ON [PRIMARY]
-GO
-SET ANSI_PADDING OFF
-GO
-CREATE UNIQUE NONCLUSTERED INDEX [IX_OpenIDAssociations] ON [dbo].[OpenIDAssociation] 
-(
-	[DistinguishingFactor] ASC,
-	[AssociationHandle] ASC
-)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
-GO
-SET ANSI_NULLS ON
-GO
-SET QUOTED_IDENTIFIER ON
-GO
-CREATE TABLE [dbo].[Consumer](
-	[ConsumerId] [int] IDENTITY(1,1) NOT NULL,
-	[ConsumerKey] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
-	[ConsumerSecret] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
-	[X509Certificate] [image] NULL,
-	[Callback] [nvarchar](2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
-	[VerificationCodeFormat] [int] NOT NULL,
-	[VerificationCodeLength] [int] NOT NULL,
-	[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
- CONSTRAINT [PK_Consumer] PRIMARY KEY CLUSTERED 
-(
-	[ConsumerId] ASC
-)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
-) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-GO
-CREATE UNIQUE NONCLUSTERED INDEX [IX_Consumer] ON [dbo].[Consumer] 
-(
-	[ConsumerKey] ASC
-)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
-GO
-SET ANSI_NULLS ON
-GO
-SET QUOTED_IDENTIFIER ON
-GO
-CREATE TABLE [dbo].[User](
-	[UserId] [int] IDENTITY(1,1) NOT NULL,
-	[FirstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
-	[LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
-	[EmailAddress] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
-	[EmailAddressVerified] [bit] NOT NULL,
-	[CreatedOn] [datetime] NOT NULL,
- CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
-(
-	[UserId] ASC
-)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
-) ON [PRIMARY]
-GO
-SET ANSI_NULLS ON
-GO
-SET QUOTED_IDENTIFIER ON
-GO
-CREATE TABLE [dbo].[Role](
-	[RoleId] [int] IDENTITY(1,1) NOT NULL,
-	[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
- CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED 
-(
-	[RoleId] ASC
-)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
-) ON [PRIMARY]
-GO
-SET ANSI_NULLS ON
-GO
-SET QUOTED_IDENTIFIER ON
-GO
-SET ANSI_PADDING ON
-GO
-CREATE TABLE [dbo].[IssuedToken](
-	[IssuedTokenId] [int] IDENTITY(1,1) NOT NULL,
-	[ConsumerId] [int] NOT NULL,
-	[UserId] [int] NULL,
-	[Token] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
-	[TokenSecret] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
-	[CreatedOn] [datetime] NOT NULL,
-	[Callback] [nvarchar](2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
-	[VerificationCode] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
-	[ConsumerVersion] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
-	[ExpirationDate] [datetime] NULL,
-	[IsAccessToken] [bit] NOT NULL,
-	[Scope] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
- CONSTRAINT [PK_IssuedToken] PRIMARY KEY CLUSTERED 
-(
-	[IssuedTokenId] ASC
-)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
-) ON [PRIMARY]
-GO
-SET ANSI_PADDING OFF
-GO
-CREATE UNIQUE NONCLUSTERED INDEX [IX_IssuedToken] ON [dbo].[IssuedToken] 
-(
-	[Token] ASC
-)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
-GO
-SET ANSI_NULLS ON
-GO
-SET QUOTED_IDENTIFIER ON
-GO
-CREATE TABLE [dbo].[UserRole](
-	[UserId] [int] NOT NULL,
-	[RoleId] [int] NOT NULL,
- CONSTRAINT [PK_UserRole] PRIMARY KEY CLUSTERED 
-(
-	[UserId] ASC,
-	[RoleId] ASC
-)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
-) ON [PRIMARY]
-GO
-SET ANSI_NULLS ON
-GO
-SET QUOTED_IDENTIFIER ON
-GO
-CREATE TABLE [dbo].[AuthenticationToken](
-	[AuthenticationTokenId] [int] IDENTITY(1,1) NOT NULL,
-	[UserId] [int] NOT NULL,
-	[OpenIdClaimedIdentifier] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
-	[OpenIdFriendlyIdentifier] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
-	[CreatedOn] [datetime] NOT NULL,
-	[LastUsed] [datetime] NOT NULL,
-	[UsageCount] [int] NOT NULL,
- CONSTRAINT [PK_AuthenticationToken] PRIMARY KEY CLUSTERED 
-(
-	[AuthenticationTokenId] ASC
-)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
-) ON [PRIMARY]
-GO
-SET ANSI_NULLS ON
-GO
-SET QUOTED_IDENTIFIER ON
+PRINT N'Creating dbo.DF_AuthenticationToken_CreatedOn...';
+
+
+GO
+ALTER TABLE [dbo].[AuthenticationToken]
+    ADD CONSTRAINT [DF_AuthenticationToken_CreatedOn] DEFAULT (getutcdate()) FOR [CreatedOn];
+
+
+GO
+PRINT N'Creating dbo.DF_AuthenticationToken_LastUsed...';
+
+
+GO
+ALTER TABLE [dbo].[AuthenticationToken]
+    ADD CONSTRAINT [DF_AuthenticationToken_LastUsed] DEFAULT (getutcdate()) FOR [LastUsed];
+
+
+GO
+PRINT N'Creating dbo.DF_AuthenticationToken_UsageCount...';
+
+
+GO
+ALTER TABLE [dbo].[AuthenticationToken]
+    ADD CONSTRAINT [DF_AuthenticationToken_UsageCount] DEFAULT ((0)) FOR [UsageCount];
+
+
+GO
+PRINT N'Creating dbo.DF_IssuedToken_CreatedOn...';
+
+
+GO
+ALTER TABLE [dbo].[IssuedToken]
+    ADD CONSTRAINT [DF_IssuedToken_CreatedOn] DEFAULT (getutcdate()) FOR [CreatedOn];
+
+
+GO
+PRINT N'Creating dbo.DF_IssuedToken_IsAccessToken...';
+
+
+GO
+ALTER TABLE [dbo].[IssuedToken]
+    ADD CONSTRAINT [DF_IssuedToken_IsAccessToken] DEFAULT ((0)) FOR [IsAccessToken];
+
+
+GO
+PRINT N'Creating dbo.DF_Nonce_Issued...';
+
+
+GO
+ALTER TABLE [dbo].[Nonce]
+    ADD CONSTRAINT [DF_Nonce_Issued] DEFAULT (getutcdate()) FOR [Issued];
+
+
+GO
+PRINT N'Creating dbo.DF_User_CreatedOn...';
+
+
+GO
+ALTER TABLE [dbo].[User]
+    ADD CONSTRAINT [DF_User_CreatedOn] DEFAULT (getutcdate()) FOR [CreatedOn];
+
+
+GO
+PRINT N'Creating dbo.DF_User_EmailAddressVerified...';
+
+
+GO
+ALTER TABLE [dbo].[User]
+    ADD CONSTRAINT [DF_User_EmailAddressVerified] DEFAULT ((0)) FOR [EmailAddressVerified];
+
+
+GO
+PRINT N'Creating dbo.PK_AuthenticationToken...';
+
+
+GO
+ALTER TABLE [dbo].[AuthenticationToken]
+    ADD CONSTRAINT [PK_AuthenticationToken] PRIMARY KEY CLUSTERED ([AuthenticationTokenId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
+
+
+GO
+PRINT N'Creating dbo.PK_Consumer...';
+
+
+GO
+ALTER TABLE [dbo].[Consumer]
+    ADD CONSTRAINT [PK_Consumer] PRIMARY KEY CLUSTERED ([ConsumerId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
+
+
+GO
+PRINT N'Creating dbo.PK_IssuedToken...';
+
+
+GO
+ALTER TABLE [dbo].[IssuedToken]
+    ADD CONSTRAINT [PK_IssuedToken] PRIMARY KEY CLUSTERED ([IssuedTokenId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
+
+
+GO
+PRINT N'Creating dbo.PK_Nonce...';
+
+
+GO
+ALTER TABLE [dbo].[Nonce]
+    ADD CONSTRAINT [PK_Nonce] PRIMARY KEY CLUSTERED ([NonceId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
+
+
+GO
+PRINT N'Creating dbo.PK_OpenIDAssociations...';
+
+
+GO
+ALTER TABLE [dbo].[OpenIDAssociation]
+    ADD CONSTRAINT [PK_OpenIDAssociations] PRIMARY KEY CLUSTERED ([AssociationId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
+
+
+GO
+PRINT N'Creating dbo.PK_Role...';
+
+
+GO
+ALTER TABLE [dbo].[Role]
+    ADD CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED ([RoleId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
+
+
+GO
+PRINT N'Creating dbo.PK_User...';
+
+
+GO
+ALTER TABLE [dbo].[User]
+    ADD CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ([UserId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
+
+
+GO
+PRINT N'Creating dbo.PK_UserRole...';
+
+
+GO
+ALTER TABLE [dbo].[UserRole]
+    ADD CONSTRAINT [PK_UserRole] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
+
+
+GO
+PRINT N'Creating dbo.FK_AuthenticationToken_User...';
+
+
+GO
+ALTER TABLE [dbo].[AuthenticationToken]
+    ADD CONSTRAINT [FK_AuthenticationToken_User] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([UserId]) ON DELETE CASCADE ON UPDATE CASCADE;
+
+
+GO
+PRINT N'Creating dbo.FK_IssuedToken_Consumer...';
+
+
+GO
+ALTER TABLE [dbo].[IssuedToken]
+    ADD CONSTRAINT [FK_IssuedToken_Consumer] FOREIGN KEY ([ConsumerId]) REFERENCES [dbo].[Consumer] ([ConsumerId]) ON DELETE CASCADE ON UPDATE CASCADE;
+
+
+GO
+PRINT N'Creating dbo.FK_IssuedToken_User...';
+
+
+GO
+ALTER TABLE [dbo].[IssuedToken]
+    ADD CONSTRAINT [FK_IssuedToken_User] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([UserId]) ON DELETE CASCADE ON UPDATE CASCADE;
+
+
+GO
+PRINT N'Creating dbo.FK_UserRole_Role...';
+
+
+GO
+ALTER TABLE [dbo].[UserRole]
+    ADD CONSTRAINT [FK_UserRole_Role] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[Role] ([RoleId]) ON DELETE CASCADE ON UPDATE CASCADE;
+
+
+GO
+PRINT N'Creating dbo.FK_UserRole_User...';
+
+
+GO
+ALTER TABLE [dbo].[UserRole]
+    ADD CONSTRAINT [FK_UserRole_User] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([UserId]) ON DELETE CASCADE ON UPDATE CASCADE;
+
+
+GO
+PRINT N'Creating dbo.AddUser...';
+
+
+GO
+SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
+
+
 GO
 CREATE PROCEDURE [dbo].[AddUser]
-	(
-	@firstName nvarchar(50),
-	@lastName nvarchar(50),
-	@openid nvarchar(255),
-	@role nvarchar(255)
-	)
+@firstName NVARCHAR (50), @lastName NVARCHAR (50), @openid NVARCHAR (255), @role NVARCHAR (255)
 AS
-	DECLARE
+DECLARE
 		@roleid int,
 		@userid int
 
@@ -236,55 +620,85 @@ AS
 	COMMIT TRANSACTION
 	
 	RETURN @userid
+
+
 GO
-ALTER TABLE [dbo].[Nonce] ADD  CONSTRAINT [DF_Nonce_Issued]  DEFAULT (getutcdate()) FOR [Issued]
-GO
-ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_EmailAddressVerified]  DEFAULT ((0)) FOR [EmailAddressVerified]
-GO
-ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_CreatedOn]  DEFAULT (getutcdate()) FOR [CreatedOn]
+SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
+
+
 GO
-ALTER TABLE [dbo].[IssuedToken] ADD  CONSTRAINT [DF_IssuedToken_CreatedOn]  DEFAULT (getutcdate()) FOR [CreatedOn]
+PRINT N'Creating dbo.ClearExpiredAssociations...';
+
+
 GO
-ALTER TABLE [dbo].[IssuedToken] ADD  CONSTRAINT [DF_IssuedToken_IsAccessToken]  DEFAULT ((0)) FOR [IsAccessToken]
+SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
+
+
 GO
-ALTER TABLE [dbo].[AuthenticationToken] ADD  CONSTRAINT [DF_AuthenticationToken_CreatedOn]  DEFAULT (getutcdate()) FOR [CreatedOn]
+CREATE PROCEDURE [dbo].[ClearExpiredAssociations]
+
+AS
+DELETE FROM dbo.OpenIDAssociation
+WHERE [Expiration] < getutcdate()
+
+
 GO
-ALTER TABLE [dbo].[AuthenticationToken] ADD  CONSTRAINT [DF_AuthenticationToken_LastUsed]  DEFAULT (getutcdate()) FOR [LastUsed]
+SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
+
+
 GO
-ALTER TABLE [dbo].[AuthenticationToken] ADD  CONSTRAINT [DF_AuthenticationToken_UsageCount]  DEFAULT ((0)) FOR [UsageCount]
+PRINT N'Creating dbo.ClearExpiredNonces...';
+
+
 GO
-ALTER TABLE [dbo].[IssuedToken]  WITH CHECK ADD  CONSTRAINT [FK_IssuedToken_Consumer] FOREIGN KEY([ConsumerId])
-REFERENCES [dbo].[Consumer] ([ConsumerId])
-ON UPDATE CASCADE
-ON DELETE CASCADE
+SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
+
+
 GO
-ALTER TABLE [dbo].[IssuedToken] CHECK CONSTRAINT [FK_IssuedToken_Consumer]
+CREATE PROCEDURE [dbo].[ClearExpiredNonces]
+
+AS
+DELETE FROM dbo.[Nonce]
+WHERE [Expires] < getutcdate()
+
+
 GO
-ALTER TABLE [dbo].[IssuedToken]  WITH CHECK ADD  CONSTRAINT [FK_IssuedToken_User] FOREIGN KEY([UserId])
-REFERENCES [dbo].[User] ([UserId])
-ON UPDATE CASCADE
-ON DELETE CASCADE
+SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
+
+
 GO
-ALTER TABLE [dbo].[IssuedToken] CHECK CONSTRAINT [FK_IssuedToken_User]
+PRINT N'Creating AutoCreatedLocal...';
+
+
 GO
-ALTER TABLE [dbo].[UserRole]  WITH CHECK ADD  CONSTRAINT [FK_UserRole_Role] FOREIGN KEY([RoleId])
-REFERENCES [dbo].[Role] ([RoleId])
-ON UPDATE CASCADE
-ON DELETE CASCADE
+CREATE ROUTE [AutoCreatedLocal]
+    AUTHORIZATION [dbo]
+    WITH ADDRESS = N'LOCAL';
+
+
 GO
-ALTER TABLE [dbo].[UserRole] CHECK CONSTRAINT [FK_UserRole_Role]
+
 GO
-ALTER TABLE [dbo].[UserRole]  WITH CHECK ADD  CONSTRAINT [FK_UserRole_User] FOREIGN KEY([UserId])
-REFERENCES [dbo].[User] ([UserId])
-ON UPDATE CASCADE
-ON DELETE CASCADE
+/*
+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)]					
+--------------------------------------------------------------------------------------
+*/
+
 GO
-ALTER TABLE [dbo].[UserRole] CHECK CONSTRAINT [FK_UserRole_User]
+
 GO
-ALTER TABLE [dbo].[AuthenticationToken]  WITH CHECK ADD  CONSTRAINT [FK_AuthenticationToken_User] FOREIGN KEY([UserId])
-REFERENCES [dbo].[User] ([UserId])
-ON UPDATE CASCADE
-ON DELETE CASCADE
+
 GO
-ALTER TABLE [dbo].[AuthenticationToken] CHECK CONSTRAINT [FK_AuthenticationToken_User]
+ALTER DATABASE [$(DatabaseName)]
+    SET MULTI_USER 
+    WITH ROLLBACK IMMEDIATE;
+
+
 GO
diff --git a/projecttemplates/RelyingPartyLogic/Utilities.cs b/projecttemplates/RelyingPartyLogic/Utilities.cs
index fb05306..023121e 100644
--- a/projecttemplates/RelyingPartyLogic/Utilities.cs
+++ b/projecttemplates/RelyingPartyLogic/Utilities.cs
@@ -11,6 +11,7 @@ namespace RelyingPartyLogic {
 	using System.Data.Common;
 	using System.Data.EntityClient;
 	using System.Data.Objects;
+	using System.Data.SqlClient;
 	using System.Globalization;
 	using System.IO;
 	using System.Linq;
@@ -40,27 +41,56 @@ namespace RelyingPartyLogic {
 
 		public static void CreateDatabase(Identifier claimedId, string friendlyId, string databaseName) {
 			const string SqlFormat = @"
-CREATE DATABASE [{0}] ON (NAME='{0}', FILENAME='{0}')
+{0}
 GO
-USE ""{0}""
-GO
-{1}
-EXEC [dbo].[AddUser] 'admin', 'admin', '{2}', '{3}'
+EXEC [dbo].[AddUser] 'admin', 'admin', '{1}', '{2}'
 GO
 ";
-			string schemaSql;
+			var removeSnippets = new string[] { @"
+IF EXISTS (SELECT 1
+           FROM   [master].[dbo].[sysdatabases]
+           WHERE  [name] = N'$(DatabaseName)')
+    BEGIN
+        ALTER DATABASE [$(DatabaseName)]
+            SET HONOR_BROKER_PRIORITY OFF 
+            WITH ROLLBACK IMMEDIATE;
+    END
+
+
+GO", @"
+PRINT N'Creating AutoCreatedLocal...';
+
+
+GO
+CREATE ROUTE [AutoCreatedLocal]
+    AUTHORIZATION [dbo]
+    WITH ADDRESS = N'LOCAL';
+
+
+GO
+"};
+			StringBuilder schemaSqlBuilder = new StringBuilder();
 			using (var sr = new StreamReader(Assembly.GetExecutingAssembly().GetManifestResourceStream(DefaultNamespace + ".CreateDatabase.sql"))) {
-				schemaSql = sr.ReadToEnd();
+				schemaSqlBuilder.Append(sr.ReadToEnd());
 			}
+			foreach (string remove in removeSnippets) {
+				schemaSqlBuilder.Replace(remove, string.Empty);
+			}
+			schemaSqlBuilder.Replace("$(Path1)", HttpContext.Current.Server.MapPath("~/App_Data/"));
+			schemaSqlBuilder.Replace("$(DatabaseName)", databaseName);
+
 			string databasePath = HttpContext.Current.Server.MapPath("~/App_Data/" + databaseName + ".mdf");
-			string sql = string.Format(CultureInfo.InvariantCulture, SqlFormat, databasePath, schemaSql, claimedId, "Admin");
+			string sql = string.Format(CultureInfo.InvariantCulture, SqlFormat, schemaSqlBuilder, claimedId, "Admin");
 
 			var serverConnection = new ServerConnection(".\\sqlexpress");
 			try {
 				serverConnection.ExecuteNonQuery(sql);
-				var server = new Server(serverConnection);
-				server.DetachDatabase(databasePath, true);
 			} finally {
+				try {
+					var server = new Server(serverConnection);
+					server.DetachDatabase(databaseName, true);
+				} catch (SqlException) {
+				}
 				serverConnection.Disconnect();
 			}
 		}
diff --git a/src/DotNetOpenAuth.sln b/src/DotNetOpenAuth.sln
index 28ea022..569a7bc 100644
--- a/src/DotNetOpenAuth.sln
+++ b/src/DotNetOpenAuth.sln
@@ -7,6 +7,7 @@ Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "DotNetOpenAuth.Test", "DotN
 EndProject
 Project("{2150E333-8FDC-42A3-9474-1A3956D46DE8}") = "Solution Items", "Solution Items", "{20B5E173-C3C4-49F8-BD25-E69044075B4D}"
 	ProjectSection(SolutionItems) = preProject
+		..\build.proj = ..\build.proj
 		DotNetOpenAuth.vsmdi = DotNetOpenAuth.vsmdi
 		..\LICENSE.txt = ..\LICENSE.txt
 		LocalTestRun.testrunconfig = LocalTestRun.testrunconfig