A fish out of water: Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created

Posted: December 3, 2018  |  Categories: Other SQL Server Tools

I was thinking in calling these post series “Note to myself”, only not to be always looking for these solutions whenever these problems not related to BizTalk Server or other integration products or technologies occur. But I decided to call it “A fish out of water” that basically is what I feel sometimes. And to start this post series I choose an occurrent “issue” that happens a lot when I’m trying to do something whit in SQL Server Management Console: Saving changes is not permitted.

The full warning message would be:

Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

SQL Server Management Console: Saving changes is not permitted

This behavior is very simple to solve and is well documented here: Error message when you try to save a table in SQL Server: “Saving changes is not permitted”. Once again, this is just for helping me not always to look at this “problem” again. My Blog is the first place I look, it is my personal notes.

Cause

This problem occurs when the Prevent saving changes that require the table re-creation option is enabled, and you make one or more of the following changes to the table:

  • You change the Allow Nulls setting for a column.
  • You reorder columns in the table.
  • You change the column data type.
  • You add a new column.

When you change a table so that you alter the metadata structure of the table, and then you save the table, the table must be re-created based on these changes. This may result in the loss of metadata and in a direct loss of data during the re-creation of the table. If you enable the Prevent saving changes that require the table re-creation option in the Designer section of the SQL Server Management Studio (SSMS) Options window, you receive the error message: Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created.

Solution

To change the Prevent saving changes that require the table re-creation option, follow these steps:

  • Open SQL Server Management Studio (SSMS).
  • On the Tools menu, click Options.
  • In the navigation pane of the Options window, click Designers.
  • Uncheck (clear) the Prevent saving changes that require the table re-creation checkbox, and then click OK.
SQL Server Management Console: Saving changes is not permitted fixed
#1 Azure Monitoring Platform
Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

59 thoughts on “A fish out of water: Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created”

  1. What is going on there? To find out “how to not prevent” took me more time than scripting a complete new database. Generations of developers seem to change … me, I’m trying to stay tune! Thanks for the hint … anyway! It worked out!

  2. This will work, but it may take a long time on a big table, and may cause down-time for your system, and may hose things in other ways depending on how many records you’re dealing with. Try it out on a test/dev system before you jeopardize the database health.

    1. That’s obvious. Nobody with common sense, not to mention knowledge of established professional practices would modify a production DB before testing the result on integration/staging/testing environment.

  3. HI,

    Kindly explain what exactly is happening if we uncheck that option. Does it mean that what ever changes we do to the table columns’ datatype will not be saved permanently and will be available only for that particular instance ?

  4. It’s not work with my instance.
    I entrace database from excel to MSSQL SERVER throut MS Visual Studio.
    Afer this action, I can’t update not null for a table inside sql server.

    What’s another way except delete it and re-create table?

    Thanks!

  5. Sando, it was an annoying issue but your simple solution resolved it just like a charm! Thanks
    Microsoft needs to add this tip along with their weired error message “Saving Changes is not permitted………..”

  6. oh my god thank you very much! i could not understand why the SQL won’t save my table, but thanks to you now i do. One of the most helpful posts i ever discovered!

  7. Hello,
    I want to extend the character length of the table from 500 to 4000 (max)
    I’m making this change
    And he wants to keep it bouncing my warning.
    If I remove the V from the option
    Will not delete all data recorded on the table?

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

turbo360

Back to Top