DACFx: DROP objects in target but not in project should optionally ignore users - by steve-jansen

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


4
0
Sign in
to vote
ID 794517 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 7/19/2013 6:50:40 AM
Access Restriction Public

Description

The DACFx option to "DROP objects in target but not in project" needs a way to optionally ignore any logins/users created for a target DB outside the DACPAC.

Understandably, DACPACs cannot define passwords for logins as the package source would compromise the passwords.  In practice, this means I typically define application role(s) in my DACPAC, and grant the necessary permissions for my app to the roles.  I then let the DBA assign whatever logins/users to these roles out of band.

Often, it is desirable to use the "DROP objects in target but not in project" option (e.g., Microsoft.SqlServer.Dac.DacDeployOptions.DropObjectsNotInSource) to ensure that any renamed objects deploy successfully.

However, this option will wipe out the logins/users created out of band by the DBA.  This is an easy way to then break the overall application, as the DBA must then recreate the logins, users, role memberships, and any permissions for the login/user.
Sign in to post a comment.
Posted by Microsoft on 2/26/2015 at 3:20 PM
Thank you for submitting this feedback. This feature will be included in an upcoming release of SQL Server Data Tools. For more information, see: http://blogs.msdn.com/b/ssdt/archive/2015/02/23/new-advanced-publish-options-to-specify-object-types-to-exclude-or-not-drop.aspx

Thanks again,
Steven Green
SQL Server Data Tools team
Posted by steve-jansen on 7/19/2013 at 6:55 AM
I attached a simple SSDT project file to demonstrate a situation that requires using the drop objects option.

Assume version 1.0 of the project creates a table:
---------------------------------------------------------------
CREATE TABLE [dbo].[Product]
(
[Id]    INT         NOT NULL    IDENTITY(1,1),
[Name] NVARCHAR(128) NOT NULL,

CONSTRAINT [PK_Product] PRIMARY KEY([Id])
)
GO
---------------------------------------------------------------


Then assume version 2.0 of the DACPAC modifies this table to be a view:

---------------------------------------------------------------
CREATE TABLE [dbo].[LegacyProduct]
(
[Id]    INT         NOT NULL    IDENTITY(1,1),
[Name] NVARCHAR(128) NOT NULL,

CONSTRAINT [PK_LegacyProduct] PRIMARY KEY([Id])
)
GO

CREATE VIEW [dbo].[Product]
AS
SELECT [Id],
     [Name]
FROM [dbo].[LegacyProduct]
WHERE [Id] > 0
GO
---------------------------------------------------------------


The deployment will fail without enabling the "DROP objects in target" option, due to SQL error:

SQL72014: .Net SqlClient Data Provider: Msg 2714, Level 16, State 3, Procedure Product, Line 2 There is already an object named 'Product' in the database.

However, enabling this option will remove any logins/users created in the DB out of band.