MS SQL Management Studio – Save changes is not permitted – The changes you have made require the following tables to be dropped and re-created

17 August 2009

If when using Microsoft SQL Server Management Studio to amend a table you encounter a dialog box saying:

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

Like this:

Then here’s how to stop this showing:

1) Open ‘Microsoft SQL Server Management Studio’

2) Click the ‘Tools’ menu and select ‘Options’

3) Expand the column on the left title ‘Designers’ and click once on ‘Table and Database Designers’.

4) Un-tick the box labelled ‘Prevent saving changes that require table re-creation’

5) Click ‘OK’

6) Try amending your table again

7) You may get a warning saying ‘Warnings were encountered during the pre-save validation process, and might result in a failure during save. Do you want to continue attempting to save?’

8) Before you click yes, make sure you won’t lose any data by making a change such as reduring the number of characters accepted or by changing the field type from say a char(255) column to a datetime one, or something simular.

9) If you’re happy, click ‘Yes’

All should be working fine and as it should now.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.