SSMS: Saving changes is not permitted error.

Symptoms

---

When you use Data Definition Language (DDL) to modify a table, and then you try to save the table in Microsoft SQL Server 2008, you may receive the following message:

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.

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 that is mentioned in the “Symptoms” section.

Workaround

---

To work around this problem, use Transact-SQL statements to make the changes to the metadata structure of a table. For additional information refer to the following topic in SQL Server Books Online:

http://msdn.microsoft.com/en-us/library/ms190273.aspx

For example, to change MyDate column of type datetime in at table called MyTable to accept NULL values you can use:

alter table MyTable alter column MyDate7 datetime NULL

Important We strongly recommend that you do not work around this problem by turning off the Prevent saving changes that require table re-creation option. For more information about the risks of turning off this option, see the “More information” section.

Error:

Resolution:

Options → Designers → uncheck “Prevent saving changes that require table re-creation”