May 23, 2014

SQL Server Management Studio - Save change is not permitted

When we try to make change in the existing table and save it in SQL Server 2008 Management Studio, we may get this error:

"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 because the Prevent saving changes that require the table re-creation option is enabled in the SQL Server Management Studio which can be found in the Designer section of SQL Server Management Studio Tools - Options. This option is enabled so that user won't be able to make any structural change through SSMS that require table re-creation such as

  • Adding a new column
  • Reordering the columns in the table
  • Changing the datatype of a column
  • Changing the Allow Nulls setting of a column

To get rid of this problem, you need to Uncheck/Turn off Prevent saving changes that require table re-creation but keep in mind that after you turn off this option, the existing change tracking information is completely deleted when the table is re-created.

To ensure change tracking information is enabled in the table you need to go to Change Tracking item which can be found in the table properties of the table and check whether the value is True or false. If the value is false, you need to make it true.

No comments: