locked
How to ignore users in VS 11 Sql Server Database project publish RRS feed

  • Question

  • I'm not sure if this is the right forum or not...

    Problem I can't figure out how to ignore users in my vs 11 beta sql server db project when publishing.

    Why is this a problem This is a problem because I want to keep the setting where I want to drop objects that are not in my project but are in the db being published to. If I delete a sproc in my project I'd like to drop the sproc in my db.

    Any ideas? I've looked at all of the publish settings and just can't seem to figure it out....

    Friday, May 18, 2012 7:25 PM

Answers

  • That only helps on the initial importing into a project.  This does not help in regular deployments to multiple environments where the user base is very disapparent.  As far as I can tell the options are:

    • You have to either check the "Drop objects in target but not in project" in the Advanced publish settings (formely known as deploy) then do a schema compare to find the drop that you would actually want to do, say like stored procedure that is no longer needed
    • Don't client the option mentioned above and plan on recreating users with all their settings on each publish.
    • Another add-on to the last option I've thought about, but never attempted, would be to setup some SQLCMD vars for each user and in a post deploy script create the user(s) with settings.  This is probably works better for application/system users that happen to have different logins.  In the profile settings for each environment you'd put the login definition inplace. 

    It all goes back to the fact that in a database project users are treated the same as any other object, which is a bad assumption that they developers made.  Users do not have a software lifecycle like a table or stored procedure.  There should be a box to ignore users.  There is one for dropping role memebership, dropping permissions, ignoring permissions, ignoring role membership, & ignorning user settings, you'd think that there would be one for the entire user object itself.  Again users have no business being in source control.

    Wednesday, May 23, 2012 11:27 AM

All replies

  • Hello Mike,

    I am trying to better understand your issue, however, I am sorry that I can't integrate your topic with your original questions very well. If you would like to delete database objects that do not exist in your database project from the target SQL Server, (for example if you delete a stored procedure in your project you'd like to delete that SP from your SQL Server), you can achieve that by the following steps:

    1). Delete that Stored Procedure from the database project.
    2). When publish the database project, on the Publish Database dialog click the Advanced button.
    3). On the Advanced Publish Settings dialog check the Always re-create database checkbox.
    4). Publish the project.

    After you publish the project successfully, that stored procedure will be deleted from the SQL Server as well.

    Thanks.


    Vicky Song [MSFT]
    MSDN Community Support | Feedback to us

    Monday, May 21, 2012 7:55 AM
    Moderator
  • Mike's problem is the fact that within the database/sql server projects Users are treated and managed the same as stored procedures.  Which does not reflect reality.  Our two options are to either click the "don't drop objects" setting then run a schema compare for the specific drops or recreate the database on every publish, when all we want to do is ignore users and their permissions.  Neither of these are good options.  Many of us work in environments where the stored procedures should be the same in across the environments, but not the users.
    Monday, May 21, 2012 11:35 AM
  • Thanks @Sean_Hawkes.  That is exactly my issue.
    Monday, May 21, 2012 11:57 AM
  • Thanks Sean for providing me the detailed information.

    Hello Mike,

    If you do not want to manage users or permissions for that database, when you import database objects and settings for the database project, you can choose to not select the Import permissions checkbox on the Import Database Wizard. See this screenshot for further information:

    Thanks.


    Vicky Song [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, May 23, 2012 10:20 AM
    Moderator
  • That only helps on the initial importing into a project.  This does not help in regular deployments to multiple environments where the user base is very disapparent.  As far as I can tell the options are:

    • You have to either check the "Drop objects in target but not in project" in the Advanced publish settings (formely known as deploy) then do a schema compare to find the drop that you would actually want to do, say like stored procedure that is no longer needed
    • Don't client the option mentioned above and plan on recreating users with all their settings on each publish.
    • Another add-on to the last option I've thought about, but never attempted, would be to setup some SQLCMD vars for each user and in a post deploy script create the user(s) with settings.  This is probably works better for application/system users that happen to have different logins.  In the profile settings for each environment you'd put the login definition inplace. 

    It all goes back to the fact that in a database project users are treated the same as any other object, which is a bad assumption that they developers made.  Users do not have a software lifecycle like a table or stored procedure.  There should be a box to ignore users.  There is one for dropping role memebership, dropping permissions, ignoring permissions, ignoring role membership, & ignorning user settings, you'd think that there would be one for the entire user object itself.  Again users have no business being in source control.

    Wednesday, May 23, 2012 11:27 AM
  • Thanks again @Sean_Hawkes.  That is exactly the issue.
    Wednesday, May 23, 2012 12:10 PM
  • Hello Mike, Sean,

    If you have imported users or permissions to the database project, and would like to ignore them when you publish the database project, you can achieve that by setting the Build Action of these objects to "None". By this setting, you will get these objects ignored when you build or publish the database project. If you have other database objects that reference these ignored users, you will get the error message complains there is an unresolved reference to that user.

    To configure the Build Action property for the user objects: right-click the User object and select Property, and then click Build Action, set the value to be "None".

    Thanks.


    Vicky Song [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, May 24, 2012 3:06 AM
    Moderator
  • This is starting to get painful.  That's just the opposite of what we want.  We do not want to keep users in the project.
    Thursday, May 24, 2012 12:53 PM
  • Vicky,

    Your suggestion did not work.  I imported a user, set the build to None on that user object, did a "clean", then "build", then a publish, I then unclicked the "Drop object not is source advanced publish setting", and the publish script that was produced had "DROP USER XXXX" in the publish script. 

    I'm not a fan of this techinque, but if it actually works i could work with it.  I still maintain that treating uses the same as tables, from a software life cycle is inaccurate.  Users, in my opinion should not be in projects, or at the least, we need an option to completely ignoreing them.

    Thursday, May 24, 2012 5:24 PM
  • Mike, if you don't want users in the project, you can delete them or not import them when you create database project just like Vicky mentioned above.
    Friday, May 25, 2012 10:50 AM
  • I don't have the users in the project.  However, when I publish, the script generated has DROP USER statements in it.
    Friday, May 25, 2012 11:52 AM
  • Hello Mike,

    If you do not want the DROP USER statement in the publish.sql file, you can take Sean's suggestion to avoid it by unchecking the "Drop objects in target but not in project" in the Advanced publish settings dialog.

    Thanks.


    Vicky Song [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, May 29, 2012 8:46 AM
    Moderator
  • Ignoring Users is not avaliable.
    This seems for me as Bug in the actual release of VSDDT.

    Situation (Something about the real developers world):
    Assuming a Database that has users (for other applications, departements....) witch are administered by an DB Admin.
    For Publish it is forbidden to delete this Users!

    Drop objects in target but not in project is not the solution!
    Why? Imagin a few weeks ago the Developer has a stored procedure in the DB Project.
    Published it and to day this procedure is not needing anymore.
    If you uncheck "Drop objects in target but not in project" this Procedure will not deleted!

    If check "Drop objects in target but not in project" the procedure is gone and all the Users!

    Setting build to none for User objects results also in a drop user ...

    Checking "Ignore user settings objects" only prevents the delete of schemas for users.

    Deleting Users in any target database that are not in a VSDDT project is definitly a wrong behavior of VSDDT!

    Whats missing? A "Ignore users" That means if this is checked Ignore any Users in target DB. 
    Thursday, July 25, 2013 8:16 AM
  • You are totaly correct. This bug is such a pain in the ass. I can't find a way to have Drop objects in target but not in project checked and avoid deleting the users on the target database. This is a little bit stupid from Microsoft. They have nothing in common with real world developers needs.

    Microsoft, -1.

    Thursday, August 1, 2013 7:42 AM
  • What I cannot fathom about this issue is that it is such a common thing to have different users in your different environments, and that MS missed this completely. I really do not want to go through the pain of sacrificing my first-born every-time I set up a db project!
    • Edited by Rake0 Thursday, August 29, 2013 12:15 AM
    Thursday, August 29, 2013 12:15 AM