While working on the table from SQL Server Management Studio, I received the warning message that saving changes is not permitted. Since I have proper permissions to do DDL operations but still faced this error.
After multiple troubleshooting techniques, I resolved this error. Here, I am sharing my real-time experience of solving this error. Let’s get started.
What are the Reasons for Save Changes is not Permitted?
This error generally arises when you try to alter the table structure, such as changing the data type. In SQL Server Management Studio, specific actions are performed by dropping the table and creating it again.
The possibility of error 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. An option Prevent saving changes that require the table re-creation is enabled by default and shows us this error.
However, you can disable this option in the settings. We will see how to do so 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. Follow the below steps to do so.
- Go to the Tools menu and 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 if it is checked already and click OK.
- Once you have disabled this option, you will no longer face this issue. However, it is not only one approach, and you should try the alternative method.
Read: Identity Column in SQL Server
What is the Alternative Approach for Error?
You don’t need to disable the Prevent saving changes that require the table re-creation option. You can also use the ALTER TABLE statement to perform some operations that generate this error.
For example, you want to change the data type of a column. If you try it without query in SQL Server Management Studio, you will face the saving changes is not permitted error.
However, you will not face this error if you do this with the ALTER TABLE statement. Note that the data type conversion of a column should be possible.
- Consider the following structure of a product 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 char (20) values.
In this way, you can also use the ALTER TABLE statement to perform other operations, such as 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 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, but it is not recommended.
Disabling this option can result in data loss. 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, I prefer the ALTER STATEMENT method for changing the meta-structure rather than the SQL Server Management Studio method, and I disable 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.
Read Also SQL Server Create Temp Table
Conclusion
Now you understand the reason behind the error. Save changes are not permitted. Once you disable this option, you are not warned when you save the table that the changes you made have changed the metadata structure of the table.
You may like the following sql server tutorials:
or Saving changes is not permitted in SQL Server.
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.
