Saving changes is not permitted Error SQL Server 2008 2012

Few days ago I have migrated one of my Database from SQL Server 2005 to SQL Server 2008. Actually what I did during the migration? I have created a Database first in SQL Server 2008. Then I have imported all of my SQL Server 2005 tables into the SQL Server 2008 Database. I thought that Everything is perfect but when I start one of my application on this database then I found that System can not insert data into few tables. I have started debugging my application and found that my imported tables has missed identity constraint. So what can I do? According to SQL Server 2005, my understanding was I can apply identity constraint on numeric columns if the column has a sequence of unique number. So I open my table (SQL Server 2008 version) in design mode then click on ID column then from Column Properties select identity = yes and then save. But at that time I found the below error:

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.

The snapshot is given below:
Sql Server Error Saving changes is not permitted

The error is self explanatory and helpful enough to find a solution clearly.

After few minutes i found the below solution:
GO TO Tools >> Options >> Designers

Then you will found the option “Prevent saving changes that require table re-creation” is checked. Just uncheck the option will resolve your problem.

Please find the step by step screenshots from below:
GO TO Tools >> Options

Now just uncheck the option will resolve your problem:

Hope it will help you.

Posted in Database, SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *