SQL Management Studio – ‘Saving changes is not permitted’ message when changing table

by

Problem

When designing a table you’re unable to save the changes.

When you try you receive the following message:

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 table to be re-created.

SQL-SavingChangesNotPermitted1

 

Solution

This message appears by design when the changes to the table require it (or another table) to be dropped and recreated.

The message warns you of the significance of a table being dropped and recreated, giving you the opportunity to backup.

Once you have backed up your database and are ready to save the changes you will need to configure SQL Management Studio to apply the changes without giving the warning. The steps below detail how to do this.

  1. With SQL Management Studio open, click on the ‘Tools’ menu then ‘Options’
  2. SQL-SavingChangesNotPermitted2
  3. In the left hand menu click on ‘Designers’
  4. Un-tick ‘prevent saving changes that require table re-creation’
  5. SQL-SavingChangesNotPermitted3
  6. Click ‘OK’ to save the changes
  7. You can now save the changes to the table.