Saving changes is not permitted SQL Server

In SQL Server 2012, I got “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

when I tried to change a column data type in a table, then click on save button to save changes in SQL Management Studio.

CreateTable

Saving changes is not permitted SQL Server

In SQL Server Management Studio, by default you can’t change the column data type after creating a table because such change requires to recreate the table again.

Saving changes is not permitted SQL Server
Saving changes is not permitted SQL Server

Prevent saving changes that require the table to be re-created

To allow saving changes that require the table to be re-created, you shoud follow the mentioned steps below:

  • Open SQL Server Management Studio.
Open SQL - Extend the SQL Evaluation period after the second expiration
  • From the menu bar, click on Tools Menu > Options.
  • From the right side, click on Designers > Table and Database Designer.
  • Uncheck “Prevent saving changes that require table re-creation”.
  • Click Ok to save changes.
prevent saving changes that require the table to be re-created
  • Go back to edit your table in Design mode that should be save successfully now without facing this warning “Saving changes is not permitted SQL Server”.

Conclusion

In conclusion, we have learned how to allow saving changes that require the table to be re-created in SQL Server Management Studio.

Applies to
  • SQL Server 2016.
  • SQL Server 2014.
  • SQL Server 2012.
  • SQL Server 2008.
You might also like to read

Leave a Reply