locked
SSDT publish settings issue RRS feed

  • Question

  • I am trying to publish to a SQL 2008 R2 instance and having issues with DROP object statements being generated for Users and Roles.  These Users and Roles do not exist in my SSDT solution and I have the following options set in the Advanced Publish Settings screen:  DROP objects in target but not in project (because I want to remove any tables, procs, views, etc that do not exist in the project anymore from my target database),  Ignore permissions, Ignore role membership and Ignore user settings objects.  Is there another option that will let me retain the User/Role objects in my target database even though they don't exist in my project?  Do I need to include these Users/Roles in my solution?

    Thanks

    Friday, April 6, 2012 12:24 PM

Answers

  • Hi,

    For users and roles, there are two options --

    1. As you pointed out correctly, including users and roles in your database project would be necessary to avoid dropping them on Publish.

    2. If you don't want to include users and roles in your project, you could use Schema Compare to 'Publish' project updates instead of Publish. In Schema Compare, click Options, and then the Object Types tab, and uncheck Users and Roles. This way, these are ignored in the comparison altogether. In order to repeat this process quickly, more similar to a publish action, you can save off the Schema Comparison, which will persist the target connection and the options you set, and include it in your project. Then each time you want to deploy project changes just open that file and Compare and Update.

    Thanks,

    Sarah


    Sarah McDevitt Program Manager SQL Server Data Tools

    • Proposed as answer by sarahmcd Thursday, April 12, 2012 6:05 PM
    • Marked as answer by Janet Yeilding Monday, April 16, 2012 7:09 PM
    Thursday, April 12, 2012 6:05 PM

All replies

  • Hi,

    For users and roles, there are two options --

    1. As you pointed out correctly, including users and roles in your database project would be necessary to avoid dropping them on Publish.

    2. If you don't want to include users and roles in your project, you could use Schema Compare to 'Publish' project updates instead of Publish. In Schema Compare, click Options, and then the Object Types tab, and uncheck Users and Roles. This way, these are ignored in the comparison altogether. In order to repeat this process quickly, more similar to a publish action, you can save off the Schema Comparison, which will persist the target connection and the options you set, and include it in your project. Then each time you want to deploy project changes just open that file and Compare and Update.

    Thanks,

    Sarah


    Sarah McDevitt Program Manager SQL Server Data Tools

    • Proposed as answer by sarahmcd Thursday, April 12, 2012 6:05 PM
    • Marked as answer by Janet Yeilding Monday, April 16, 2012 7:09 PM
    Thursday, April 12, 2012 6:05 PM
  • I had the same issue, I had to include them in my project but this is not very good approach, as it requires maintenance in two places.  Technically what SSDT is correct as it should remove all objects not in the model, however I belive the SSDT team can work on this to improve and trully respect the Ignore Role Membership property in all cases.

    Sarah -- I am using the DACPAC output to publish it using SQLPackage command line utility as itis part of automated build/deploymnet process.  Publish does not necessary mean publish by user invoking publish in VS or otherwise. While the second option you are recomending will work it is still not helping when automating build/deploy.

    • Edited by GorancoB Thursday, April 12, 2012 6:36 PM respond to Sarah on options
    Thursday, April 12, 2012 6:31 PM
  • Hi Sarah, I have a similar problem which the Schema compare workaround (instead of publishing) IS STILL NOT WORKING.

    I'm working with SSDT 2012 with a SQL Server 2005. I unchecked users & roles (and even Application Roles) and done a schema compare. Suprisingly, objects of type "Schema" were found missing in project. these schemas are actually users schema, created once in our production db. adding the schema fails the build because now the users are missing.

    And again, if I want the schema compare (like the publish) to work automatically, I need to also add the users to the project so that it will be identical and the project would build. And if I want to compare and sync to another 2nd DB, with different users defined? problematic.

    Would love to hear a solution,

    Eyal.

    Saturday, April 28, 2012 9:48 PM
  • I just want to wake up this thread as it appears that the issue is getting bigger for me now.  It was all fine and dandy when the database was managed and deployed internally as I woudl have known which users are needed in the DB and have them included in the project. Now that the database in in production whcih is hosted by 3rd party i can no longer afford to drop any users that are not in the project. In an effort of trying to see if I can trick the publish not to drop users I tried DropRoleMembersNotInSource and IgnoreRoleMembership, but both appear to work fine as the users are dropped before those two are evaluated which means the users role memebrhsip will be adjusted. meaning user will get removed from the roles by drop user action.  

    I believe we need another publish/script parameter IgnoreUsersNotInSource to accomodate this specific case. This parameter will automatically assume that DropRoleMembersNotinSoruce is false and IgnoreMembership is false (possible other parameters will need to be set as false like IgnorePermissions).

    I am sure that with time more use cases will pop-out, so this is definitly something that I would recomed to SSDT Team to look after.

    
    
    
    Tuesday, May 15, 2012 6:19 PM
  • I agree - MS please add something like IgnoreUsersNotInSource or something... I am finally going to production with this product and it's a pain in the ass the dropping of Users!! I need to devise all sort of work arounds for this bug...

    Please add this feature (or fix the one's not working) ASAP!

    Tuesday, June 12, 2012 7:26 PM
  • Here's another vote to add this feature to SSDT i.e. "Ignore Users" during publish.  Our list of users is also different in DEV, QA and Production.

    Deepak

    Friday, September 28, 2012 5:15 PM
  • Yes we need more votes on this - but I guess this is wrong place to do it - has anyone opened an issue on MS Connect (the proper place to vote and have it done).

    Anyhow my workaround for interested readers is to recreate the user in the post deployment script. 

    USE [$(DatabaseName)]
    GO
    IF NOT EXISTS (Select principal_id from sys.database_principals where name like 'YourUser') 
    BEGIN
       PRINT 'Creating Database User'
       CREATE USER [YourUser] FOR LOGIN [YourLoginForYourUser] WITH DEFAULT_SCHEMA=[dbo]
       PRINT 'Setting YourUser as member of db_owner role'
       EXEC sp_addrolemember N'db_owner', N'YourUser'
    END
    GO

    Friday, September 28, 2012 5:34 PM
  • I've created a suggestion on MS Connect for this. If you're interested in this issue, please take a look and vote/comment on it:

    https://connect.microsoft.com/SQLServer/feedback/details/775839/ssdt-add-publication-setting-to-ignore-database-users

    Saturday, January 5, 2013 1:33 AM
  • I have a little bit different problem. In my scenario we replicated databases were not all objects are replicated. Then when deploying, we need to have two set deployment scripts.

    We need to have a script to run only on the publisher database and a different one that will run on the publisher and subscriber databases. They both differ on the object types being deployed. 

    To simplify my problem, on the publisher database I want to deploy only tables, and, then on the subscriber only stored procedures and views.

    I can achieve this using schema compare. Currently I have a PublisherOnly schema compare file and another one for PublisherAndSubscriber. They both have the ignore object type set accordingly.

    When it comes to automate the deployment/publishing, I can't. I can't define which object types will be included or not for publishing, and I can't define which schema compare file will be used.

    I also can't use schema compare files for automation (as far as I know).

    Does anyone have any suggestion on how I could automate the publishing of only a subset of object types from my project?

    I would love to have the same options I have in schema compare files in the publish profiles, or, be able to specify a schema compare file to be used during publish process...

    Solari

    Wednesday, April 10, 2013 1:24 PM