Saving changes is not permitted
January 5, 2012
Posted by on
For my recent project, I was creating some tables using the SQL server designer. After creating tables, I tried to change the primary key to be of Identity column.
The change could not be saved and I received the following error message.
Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either…
It’s a defensive mechanism that SQL server offers us to prevent accidental loss of data due to unintentional changes. It is good to have this system on.
Following list of actions might require a recreation of the table, deleting the existing records.
Adding a new column to the middle of the table
Dropping a column
Changing column nullability
Changing the order of the columns
Changing the data type of a column
However, if you are sure enough that the change is intentional and you are aware the the table would be deleted and re-created, then you may proceed with the following step to override the defense system.
Select the Tools menu –> Options in the SQL server management studio.
In the Options dialog box, expand the Designers on the tree view and select Table and Designers. On the right hand side, uncheck the option, Prevent saving changes that require table re-creation.
Then select Ok. Now the designer should allow you to save the changes.
Again, be aware that when you save the changes, the entire table might be deleted and the existing records might get lost to accommodate the change.
Reference: Save (Not Permitted) Dialog Box