This post was originally published here
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.
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.
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.
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 check box, and then click OK.