When working with SQL Server Management studio, if you use the Design view of a table and attempt to make a change that will require the table to be dropped and re-added, you may receive an error message like this one:

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 cant be re-created or enabled the option Prevent saving changes that require the table to be re-created.

In truth, its quite likely that you didnt enable such an option, despite the error dialogs accusations, as it is enabled by default when you install SQL Management Studio.  You can learn more about the issue in the KB article, Error message when you try to save a table in SQL Server 2008: Saving changes is not permitted.

Warning: As the above article states, it is not recommended that you turn off this option (at least not permanently), as it will ensure that you do not accidentally change the schema of a table such that data is lost.  Do so at your peril.

The simplest way to bypass this error is to go into Option Designers and uncheck the option Prevent saving changes that require table re-creation.  See the screenshot below.

The main reason why you will see this error is if you attempted to do any of the following to the table whose design you are saving:

• Change the Allow Nulls setting for a column
• Reorder columns
• Change any columns data type
• Add a new column

The recommended workaround is to script out the changes to a SQL file and execute them by hand, or to simply write out your own T-SQL to make the changes.

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.