Saving changes is not permitted in SQL Server

In this article, we will discuss the reasons behind the “Saving changes is not permitted in SQL server” error. You will know how you can solve this error. Also, you will see some examples of this situation.

• What are the reasons?
• How can we fix it?
• What is the alternative?
• The best approach to solve the issue

What are the reasons?

Generally, this error arises when you try to alter the table structure like changing the data type. In SQL Server Management Studio, there are certain actions for which it drops the table and creates it again.

The possible actions can be:

• You are trying to reorder the columns.
• Trying to add a new column.
• Changing the data type of a column.
• Change the Allow Null settings for a column.
• Change the table filegroup or its text/image data

Dropping the table can result in data loss. Therefore, the SQL Server Management Studio prevents this action and gives us a warning. There is an option “Prevent saving changes that require the table re-creation“ that is by default enabled and shows us this error.

However, you can disable this option in the settings. We will see how we can disable this option in SQL Server Management Studio.

How can we fix it?

You can remove this error by disabling the the Prevent saving changes that require the table re-creation option in settings. The following steps will guide you to disable this option:

• Go to Tools menu, click Options to open the options window.

• Click on Designers options and uncheck the option Prevent saving changes that require the table re-creation to disable the option and click OK.

• Once you have disabled this option, you will no longer face this issue.
• However, it is not a good approach and you should try the alternative method.

Read: Identity Column in SQL Server

What is the alternative?

It is not necessary that you disable the Prevent saving changes that require the table re-creation option. You can also use the ALTER TABLE statement to perform some of the operations that generate this error.

For example, you want to change the data type of a column. If you will try it without query in SQL Server Management Studio, you will face the saving changes is not permitted error.

However, if you do this with the ALTER TABLE statement, you will not face this error. Note that the data type conversion of a column should be possible.

• Consider the following structre of a table ProductsTable.

• Let us say you want to change the data type of the ProductID column from int to nchar(20). You will write the ALTER TABLE statement as:

ALTER TABLE dbo.ProductsTable
ALTER COLUMN ProductID nchar(20)

• Now refresh the table and check its structure again.

• Also, if the column had integer values earlier, the SQL Server will convert these values into nchar(20) values.

In this way, you can use the ALTER TABLE statement to perform other operations also like adding or removing the NULL property.

Read: Delete Duplicate Rows in SQL Server

The best approach to solve the issue

You should always use the ALTER TABLE statement to perform the operations like changing a column’s data type, adding or removing the NULL or NOT NULL constraints etc.

You can disable the Prevent saving changes that require the table re-creation option. However, it is not recommended to do so.

Disabling this option can result in data loss. This is because when you alter the table’s meta-structure, the SQL Server Management Studio will drop the table first and create it again.

Dropping of the table can result in the loss of various information. For example, in SQL Server, there is a feature named Change Tracking. If you have enabled this option, the SQL Server tracks changes to the table. If the table is dropped, this tracking information will also be deleted.

Thus, prefer to use the ALTER STATEMENT to change the meta-structure rather than using SQL Server Managemen Studio method and disabling the Prevent saving changes that require the table re-creation option.

Otherwise, disable this option only if you are not using the Change Tracking feature.

You may like the following sql server tutorials:

• SQL Server Convert Function
• SQL Server Convert Datetime to date
• Exception Handling in SQL Server
• SQL Server convert integer to string
• SQL Server Create Temp Table

In this sql server tutorial, we learned how to fix errors, Saving changes is not permitted in SQL Server. The reason for the error and fixes for Saving changes is not permitted in SQL Server.