SQL Server: SQL Server 2008: Saving changes is not permitted

SQL Server 2008: Saving changes is not permitted

Posted Date: 04-Jul-2010	Category: MS SQL Server	Rating:
Author: Super Guy	Member Level: Diamond	Points: 15

This article talks about the SQL Server 2008 problem '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 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'.

Prevent changes that require table re-creation

Prevent 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.

Post Comment:

Notify me by email when others post comments to this article.

Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.

No HTML formatting and links to other web sites are allowed.

This is a strictly moderated site. Absolutely no spam allowed.

Name:	Sign In to fill automatically.
Email:	(Will not be published, but required to validate comment)
	Type the numbers and letters shown on the left.