I have been automating database deployment using SQL Server Data Tools, ms build and Jenkins. I am using a dummy publish profile
<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<PropertyGroup>
<IncludeCompositeObjects>True</IncludeCompositeObjects>
<TargetDatabaseName>AdventureWorksDW</TargetDatabaseName>
<DeployScriptFileName>AdventureWorksDW.sql</DeployScriptFileName>
<TargetConnectionString>Data Source=.;Integrated Security=True;Pooling=False</TargetConnectionString>
<BlockWhenDriftDetected>False</BlockWhenDriftDetected>
<RegisterDataTierApplication>False</RegisterDataTierApplication>
<BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
<IgnorePermissions>True</IgnorePermissions>
<CreateNewDatabase>True</CreateNewDatabase>
<ProfileVersionNumber>1</ProfileVersionNumber>
</PropertyGroup>
</Project>
I am then overriding certain values within this using the property switch with ms build.
Despite not including permissions in the project and setting IgnorePermisssions to true I get the following output from ms build.
Microsoft (R) Build Engine version 4.0.30319.17929
[Microsoft .NET Framework, version 4.0.30319.17929]
Copyright (C) Microsoft Corporation. All rights reserved.
Build started 05/09/2012 17:35:28.
Project "E:\Jenkins\workspace\Publish SSDT with parameters\Database\ETL POC\AdventureWorksDW\AdventureWorksDW.sqlproj" on node 1 (Publish target(s)).
SqlPublish:
Deployment script generated to:
E:\Jenkins\workspace\Publish SSDT with parameters\Database\ETL POC\AdventureWorksDW\bin\Debug\AdventureWorksDW.publish.sql
Creating AdventureWorksDW...
Creating [My-Domain\jenkins]...
E:\Jenkins\workspace\Publish SSDT with parameters\Database\ETL POC\AdventureWorksDW\bin\Debug\AdventureWorksDW.publish.sql(173,1,173,1): Deploy error SQL72014: .Net SqlClient Data Provider: Msg 15063, Level 16, State 1, Line 1 The login already has an account under a different user name. [E:\Jenkins\workspace\Publish SSDT with parameters\Database\ETL POC\AdventureWorksDW\AdventureWorksDW.sqlproj]
An error occurred while the batch was being executed.
Done Building Project "E:\Jenkins\workspace\Publish SSDT with parameters\Database\ETL POC\AdventureWorksDW\AdventureWorksDW.sqlproj" (Publish target(s)) -- FAILED.
Build FAILED.
EDIT:
I have now archived the publish script from msbuild. The section below has been edited to excluded irrelevant database options being set and ends at the command which raises the error.
/*
Deployment script for AdventureWorksDW
This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
GO
:setvar DatabaseName "AdventureWorksDW"
:setvar DefaultFilePrefix "AdventureWorksDW"
:setvar DefaultDataPath "E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"
:setvar DefaultLogPath "E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"
GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF;
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
BEGIN
PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
SET NOEXEC ON;
END
GO
USE [master];
GO
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 = [$(DatabaseName)], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
LOG ON (NAME = [$(DatabaseName)_log], FILENAME = N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE Latin1_General_CI_AS
GO
GO
PRINT N'Creating [MY-DOMAIN\jenkins]...';
GO
CREATE USER [MY-DOMAIN\jenkins] FOR LOGIN [DR-FOSTER\jenkins];
I have ran this script outside of Jenkins and it succeeds but that is under a different used account.
It seems as though despite MY-DOMAIN\jenkins creating the database and therefore being the owner the publish script wants to then create a user and set that as db_owner.
Does any one have any experience doing similar? This is starting to feel like a bug.
Thanks