Sql Server 'Saving changes is not permitted' error ► Prevent saving changes that require table re-creation

user_842663 0
Viewed 598425

When I create a table in SQL Server and save it, if I try to edit the table design, like change a column type from int to real, I get this error:

Saving changes is not permitted. The change you have made requires the following table 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.

Why do I have to re-create the table? I just want to change a data type from smallint to real.

The table is empty, and I didn't use it until now.

menuitem save ssms sql-server-2008
16 Answers

This can be changed easily in Microsoft SQL Server.

  1. Open Microsoft SQL Server Management Studio 2008
  2. Click Tools menu
  3. Click Options
  4. Select Designers
  5. Uncheck "Prevent saving changes that require table re-creation"
  6. Click OK

enter image description here

user_60577870

If you use sql server Management studio go to Tools >> Options >> Designers and uncheck “Prevent Saving changes that require table re-creation” It works with me

user_80107680

If you can not see the "Prevent saving changes that required table re-creation" in list like that The image

You need to enable change tracking.

  • Right click on your database and click Properties
  • Click change tracking and make it enable
  • Go Tools -> Options -> Designer again and uncheck it.
user_13026910

Actually, You are blocked by SSMS not the SQL Server.

The solution are either change setting of SSMS or use a SQL query.

Using SQL Query you could do the update freely. Example you want to add a new column to a table, you could do like this :

ALTER TABLE Customers ADD Email varchar(255) NOT NULL DEFAULT 'OK';

Other option is changing SSMS setting. Please refer to other answer, as many has explain it.

user_103513160
Related