Solution for SQL Server Error “Saving Changes is Not Permitted”

Posted March 31, 2015 by Vishwanath Dalvi in Database, SQL Server

feature-sql1

By default, SQL server has added some limitations. For instance, changing data types in design mode, adding a column in the middle of two existing columns, and changing the null-ability of an existing column are not permitted to change from design mode in SQL server management studio (SSMS) because they require a table to be dropped and created again. Here is a solution for the error these create.

The following actions might require a table to be re-created as per msdn.

      • 1. Adding a new column to the middle of the table
        2. Dropping a column
        3. Changing column null-ability
        4. Changing the order of the columns
      5. Changing the data type of a column

 

In the figure below, I tried to change the length of a column name from varchar(100) to varchar(150). It did not allow me to make this change and threw the error below.

savings changes not permitted in sql server

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 cannot be re-created, or you have enabled the option Prevent saving changes that requires the table to be re-created.

SQL server has included a feature to allow users to edit tables in design mode.

Follow these steps to override the default settings and solve the error “Saving changes is not permitted.”

1. In SSMS, go to Tools in the Main Menu.
2. Select Options from Tools.
3. Select and expand Designers.
4. Uncheck the box Prevent saving changes that require table re-creation.
5. Click OK. This will save your applied settings automatically.

Saving changes in SQL server

Now, if you try to alter the table in design view, SQL server will accept your input and will not give the error saving changes is not permitted.

The best method for making changes in table structure is through T-SQL code, which works perfectly, and we can run same code on multiple servers if we have to make the same changes over multiple databases having the same table structure.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.