When using MS SQL Server Management Studio 2008, the following error occurs when making changes to a table that require the table to be re-created (I.E. the changes cannot be accomplished using ALTER statements):

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.
The issue is almost always related to the latter possibility mentioned in the error message - the option "Prevent saving changes that require the table to be re-created" is enabled. This option is enabled by default when you install MS SQL Server Management Studio 2008 and will prevent you from making any changes that require the table to be re-created.

The reason for this is that those types of changes could potentially cause data to be lost if a column can no longer hold the type of data it contains(or if you remove a column completely). However, as long as you consider this when making structure changes, you can safely make these types of changes to tables.

Below are steps to disable the option so that you can make any change to a table without getting the error:

  1. From the 'Tools' drop-down menu, select 'Options...'
  2. Expand 'Designers'
  3. Select 'Table and Database Designers'
  4. Uncheck 'Prevent saving changes that require table re-creation'
  5. Click 'OK'



David D.