Thursday, May 17, 2012

Saving changes is not permitted?!

So, you are working on a database where you have DDL permissions, if not db_owner, but SQL Server Management Studio barks at you because you want to insert a new column in the middle of the table. The message you get looks something like this [emphasis added]:
Saving changes is not permitted. The changes you have made require the following tables ot 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.
<List of tables>
What's that? You didn't enable that option? You probably didn't, but that just so happens to be the default when you install SSMS. It's easy to disable this option if you know where to look. Before you do, however, you need to know that you may lose all the data in the table because SSMS is going to drop and re-create it for you, that's what "re-create" means.

To disable this option, bring up the Options dialog (Tools --> Options) and go to the Table and Database Designers section under Designers in the menu on the left. You'll find the Prevent saving changes that require the table to be re-created option at the bottom of the Table Options block as shown in the screenshot below.
SQL Server Management Studio - Table and Database Designers Options dialog

This applies to SQL Server Management Studio 2012 as well as 2008 R2.

1 comment:

  1. Thank you for the post! I was able to save changes to my table now.