SQL Server Error "Saving changes is not permitted"

In SQL Server Management Studio, while modifying table design through Design option, users often come across the error "Saving changes is not permitted..." as shown below.


Error Dialog

 

The complete error states that:

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 problem occurs when the "Prevent saving changes that require the table re-creation" option is enabled and one or more following changes are made to the table:

• Changing the Allow Nulls setting for a column
• Reordering the columns
• Changing the column data type
• Adding a new column

When a table is changed such that it alters the metadata structure of table, it requires re-creation of table based on the changes. This may result in loss of metadata and loss of data during re-creation of table. If the "Prevent saving changes that require the table re-creation" option in Designer section of SQL Server Management Studio Options window is enabled, this error message is displayed.

 

To avoid this error, T-SQL statements are to be used to make changes to the  metadata structure of table. For example, to change a column to accept NULL values, we can use:

alter table Table1 alter column Column1 int NULL

 

It is not recommended to disable "Prevent saving changes that require the table re-creation" option to by pass this error.

 

Turning off this option will definitely help in re-creating a table, but it also leads to changes being lost. For instance, we may have enabled the Change Tracking feature to track changes to the table. If we turn of this option, when a table is re-created, the existing change tracking information is deleted. Hence it is not recommended to turn off this option.

 

...Ssk