Saving changes is not permitted - SQL Server error message Solution

I was using SQL Server 2005 for a while. Recently I upgraded the SQL Server 2005 to SQL Server 2008 in an effort to take advantage of the new cool features in it. The initial test went through fine and all applications were running fine.

Recently, I tried to edit one of the tables and found that it is not allowing me to save the table changes. I had never seen such an error message before. This was the error message I got:

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 tat can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.



I spent a while thinking it is something wrong with the table or something wrong with the type of change I making. I gave up after 1-2 minutes of efforts. Next day, I had to edit another table just to increase the size of a field and it resulted in exact same problem. This time I thought something seriously wrong with my SQL Server. After a bit of research, I found that I had to change some settings to allow any kind of changes in SQL Server 2008 from the management console that require dropping and recreating the table.

Here is the steps to fix this problem:

1. Open Management Console

2. Click on the "Tools" menu

3. Click on "Options"

4. Expand the tree node "Designers"

5. Click on "Tables and Database Designers"

6. Uncheck the checkbox 'Prevent saving changes that require table re-creation'.



Press 'OK' in the dialog box and you are done. The error will not occur any more when you try to save the table schema from SQL Server management console.