..::\\ www.christiano.ch //::..

SQL Server 2008: Error Saving changes is not permitted

If you have been playing with SQL Server Management Studio 2008 and are modifying tables in the designer, you may run into this error: Save 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.

Here’s a screenshot:

In the 2008 , they have added a new option to prevent saving of changes that will require a table re-creation.  If you think about it, this is a good thing.  If you are running a production database, you want maximum performance.  If Management Studio needs to re-create a table to apply a change that means it needs to create a copy of the table, transfer the data, create the new table, move the data over, delete the old stuff, etc.  This can potentially be a major operation. 

However, if you don’t want this feature enabled or our just working with a development or test database, it is easy to turn off.  Just open up the Options window in Management Studio and go to “Designers –> "Table and Database Designers”, find the “Prevent saving changes that require table re-creation” option, and turn it off.