[Solved] “Saving changes is not permitted” on SQL Server Management Studio

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.
Modify Database Table

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.
Saving changes is not permitted

And when you click cancel, you will see this message dialog.
User canceled out of save dialog
User canceled

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”

  1. On Microsoft SQL Server Management Studio, select Tools -> Options.
    Open Options
  2. 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.
    Uncheck Prevent saving changes that require the table re-creation
  3. Now you can save changes on the table.
    Confirm Save Changes

Reference

4 Comments

  1. Bob Marley September 19, 2012
  2. Chris Barba July 17, 2013
  3. vương xuân sơn September 18, 2016
  4. HH December 21, 2016

Leave a Reply

%d bloggers like this: