DROP DEFAULT and RULE objects without dropping Users

Question

I have a legacy database schema that uses DEFAULT and RULE objects that I would like to migrate to Default and Check constraints.

I've found that if I simply delete the DEFAULT and RULE objects from my project, the change scripts will leave the existing DEFAULT and RULE objects in place unless I enable the advanced 'DROP object in target but not in project' option on the Publish Database dialog.

Using this option and deleting the DEFAULT and RULE objects from my project does cause SSDT to script unbinds and DROPs of the DEFAULT and RULE objects. Unfortunately it also scripts the removal of custom database users as well.

The custom database users and their corresponding logins are not part of the database project because they need a custom name, something that doesn't seem to be possible in the current version of SSDT for Visual Studio 2010. I'm creating custom database users because our application requires the same database schema to be deployed many times on the same server, with each database having a separate login and corresponding user. I've scripted the creation of the custom users, schemas, and logins in the pre-deployment scripts.

Is there any way I can drop the DEFAULT and RULE objects in my existing databases without deleting existing users? Alternately, is there a way to customize the name of database users while keeping them as schema objects in the database project?

---

David Johnston

Answer 1

Hey David,

I think there are a couple questions at play here. If I understand you correctly, your situation is that you have a table with a rule and default defined inside your project and users outside your project in your pre-deployment script.

If this is correct, the reason you are seeing the drop of the user is that the user isn't technically defined inside the project, so it isn't really the dropping of the rules/default that is causing the user drop, it's the fact that they aren't within the scope of the project. So you would see this same behavior if you left the rule/default in place and simply enabled the drop objects option as the user is defined outside the project.

As for your second point, by customizing the users, do you mean you would like to change the name of the user depending on the deployment environment/name of the database?

Thanks and hope this helps a bit, let me know if you have any more info,

Adam

---

Adam Mahood - Program Manager - Data-Tier Application Framework (DACFX)/SQL Server Data Tools

Answer 2

Thanks for the reply Andy.

Sorry for the late reply.

With respect to the issue of Default and Rule objects, I guess what I really wanted was a way to drop them from the target database without having to enable the "DROP object in target but not in project" option on the Publish Database dialog. This option is too general and will delete other objects I don't want deleted like custom created database users. The Advanced Deployment Options like "Drop constraints not in source" and "Drop indexes not in source" are similar to what I want, so why not add options for Default and Rule objects?

With respect to the custom users issue, what I want do is customize the name of user and login objects at deployment time. In our case the user and login names are usually based on the database name but that shouldn't be a requirement. Having a customizable user in the project rather than as part of a post-deployment script would mean I could actually turn on the "DROP object in target but not in project" option on the Publish Database dialog.

Thanks for your help.

---

David Johnston

Answer 3

Hey David,

Thank you for the feedback. With regards to the ability to drop only default and rule objects, we currently do not offer the granularity of all object types when choosing to drop objects not in source on deployment. The best way to accomplish this is to use Schema Compare to do a selective deployment where you can exclude the objects you do not want to drop.

We have also heard feedback from other folks in the realm of having customizable users and logins inside the project that can be set at deployment time. I will capture your feedback and we will continue to look at this for inclusion in a future release of DACFx/SSDT. Right now

Thanks again for your feedback and let me know if you have any additional questions/comments.

Adam

---

Adam Mahood - Program Manager - Data-Tier Application Framework (DACFX)/SQL Server Data Tools