SQL Server 2008: FIXED “Saving changes is not permitted”

Working in SQL Server 2008 I received this confusing error when editing the structure of a table:

“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.”

I tried many things but I kept getting this error. After some trouble shooting I found the following information:

This error happens because “Saving Changes is not permitted” when using SQL Server 2008 Management Studio to modify the structure of a table especially when SQL Server needs to drop and recreate a table to save the structural changes. It is always advised to make a structural change for a table using TSQL. However, it is a convenient option for database developers to use SQL Server Management Studio make such changes  as the  Prevent Saving Changes That Require Table Re-creation option is enabled by default in SQL Server 2008 Management Studio.

I can understand why this feature would be helpful to have but to have it enabled by default is just silly.

I found that you can disable this feature by:

  1. Open the Tools menu then click on Options
  2. Open the “Designers” tree
  3. Click on “Table and Database Designers”
  4. Uncheck “Prevent saving changes that require table-creation”
  5. Press “Ok”

Once I disabled the “Prevent saving changes that require the table re-creation” option,  I was ahead and save the changes I needed to make.