“Saving Changes is Not Permitted” in SQL Server 2008 Management Studio

When you try to modify the structure of a table that requires dropping and recreating a table to save the changes, like adding a new column between existing columns, changing column nullability or column data type in an existing table in SQL Server 2008 Management Studio Designer, you will get the following error message:

Saving changes is not permitted. The changes 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.

After clicking the cancel button you will receive the following dialog box:

User canceled out of save dialog

You will get this error message only if you attempt to make changes using SQL Server Management Studio, the same operation will work using T-SQL in Query Editor.

Modifying the structure of a table that requires dropping and recreating a table is very resource intensive and causes locks, so be careful when disabling this option. To disable it in Management Studio, on the Tools menu, click Options:

Select Designers:

Uncheck the "Prevent saving changes that require table re-creation" check box (by default, this option is checked):