If you modify structure of database table which requires the table to be recreated and try to save it in SQL Server 2008, you may receive an error message. For example, I modify Categories table in Northwind database by check AllowNulls on CategoryName column as the figure below.
Then, I try to save changes of the table, I will see the error message as following:
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.
This error message occurs because of the default option on SQL Mangement Studio which prevents you to save changes on a table that require the table re-creation. So why prevent this? Because there may be a chance of data loss when you save changes on a table if the table need to be re-created. You can read about this by follow the link on reference section at the end of this post.
Microsoft recommends you to use Transact-SQL statements to make the changes of a table. But if you don’t want to write such statements, or you are working on test environment, or you do have a backup of database, you can simply turn off this option by follow instruction below.
Step-by-step to solve “Saving changes is not permitted”
- On Microsoft SQL Server Management Studio, select Tools -> Options.
- On Options, expand Designers and select Table and Database Designers. Then, uncheck the Prevent saving changes that require the table re-creation check box and click OK.
- Now you can save changes on the table.