Error in SSMS on SQL Server 2008: "Saving changes is not permitted".

Peter Schmitz

Administrator
Staff member
We all have to make changes to our tables every now and then. Columns might have to be added or removed, moved around within the table definition, made NULLABLE or not to accept NULLS, or perhaps the column definition originally built proves to be too conservative, and thus column lengths will have to be altered to allow for bigger values.

Rather than doing this by scripting, personally I will use the table designer from SSMS, as that allows me to work much faster than having to build up the command manually (and seeing I do not believe in blindly memorizing SQL Scripting syntax, it also involves using BOL to be reminded of the exact syntaxes to be used).

Whenever you use the UI Designer (which can be accessed by right-clicking the table and selecting 'Design', as shown in the screenshot below), there's a caveat, though.

Design.png

Any of the following changes require the table to be dropped and then recreated:​

  • Reordering columns
  • Adding a new column
  • Making changes to the "Allow NULL" setting of a column
  • Changing the data type of a column
If you are working on a default installation, you will then see the following error:

saving_changes_is_not_permitted.png

"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 recreated or enabled the option Prevent saving changes that require the table to be re-created."

To circumvent the error, from within SSMS head over to the top-menu, click Tools, and then select Options:​

Menu_Tools_Options.png

In the Options screen, select "Designers", and on that screen, uncheck the box next to "Prevent saving changes that require table re-creation":​

options_designers.png

That's it. The nasty error message should now no longer pop up.​

One additional comment is that if you have "Change tracking" configured on the table in question, re-creating the table will wipe out whatever "Change tracking" has been stored so far.​

If you do have "Change tracking" enabled, and cannot afford to lose this information, leave the option above as it was, and use T-SQL to update the tables.​
 
Top