If at all possible, this should be fixed from the deployment side of things and not from a questionable modification of the target database side of things (referring to the conversation in the comments on the Question related to using the deprecated sp_addalias
).
Depending on what the exact problem is, there are various configuration options of DacPac deployment that can be used to get around such conflicts. These options can either be specified on the command line if running SqlPackage, or can be placed into a DAC Publish Profile (i.e. an XML config file).
The options of interest here are listed in the Publish Parameters, Properties, and SQLCMD Variables section of the SqlPackage
page.
To start with, do you have any of the following set to True
:
DropObjectsNotInSource
(default: False)
DropRoleMembersNotInSource
(default: False)
IgnoreLoginSids
(default: True)
If you have the DropObjectsNotInSource
property set to True
, then try adding:
DoNotDropObjectType=Users
ExcludeObjectType=Users
IgnoreRoleMembership=True
IgnoreUserSettingsObjects=True
??
If needing to specify the ExcludeObjectType
property, that would look as follows, depending on where it is specified:
- On the command-line:
/p:ExcludeObjectType=Users
In a DAC Publish Profile:
<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0"
xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<PropertyGroup>
<ExcludeObjectType>Users</ExcludeObjectType>
</PropertyGroup>
</Project>
EXEC sp_addrolemember 'db_owner', 'your user'
. Note that sp_addrolemember is announced as to be deprecated as of sql server 2014. – Kin Shah Nov 9 '15 at 18:20CREATE USER foo FROM LOGIN foo;
? – Aaron Bertrand Nov 9 '15 at 18:30This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
– Aaron Bertrand Nov 9 '15 at 19:39