Bernie Cook's Blog

Azure, C#, .NET, Architecture & Related Tech News

Saving Changes is Not Permitted – SQL Server

2 Comments

Whether you’re using SQL Server 2008 R2 Management Studio or SQL 2012 Management Studio you may come across this particular warning when attempting to commit changes to an existing table:

“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 recreated.”
Save
Don’t be alarmed, it’s just SQL Server warning you that if you do save the table changes SQL Server is going to drop and re-create the table in the background.

Solution

Unfortunately there isn’t an option to accept or cancel the operation from the dialog so to get around that you need to perform the following steps:

  1. In SQL Server Management Studio (SQL 2008 R2, SQL 2012) select the Tools menu option
  2. Select the Options… choice from the menu
  3. When the Options dialog appears navigate to the root option: Designers (shown below)
  4. Uncheck the option “Prevent saving changes that require table re-creation
  5. Click OK

And you’re done. You can return to the table designer and save the changes without the above dialog getting in the way.
Options
Good luck.

Advertisements

Author: Bernie

I currently live and work in the UK, just outside of London. I've been working in IT for 15+ years and in that time have solved many technical problems via blogs, forums, tutorials etc. and feel like I should be giving something back. This blog post is my way of contributing and I hope it proves just as useful to others as their contributions have to me.

2 thoughts on “Saving Changes is Not Permitted – SQL Server

  1. Using Management Studio to make changes do tables is a pretty risky thing to do. It will usually drop and recreate objects, and if all your systems and databases are nice to each other this would never be an issue, but sometimes it breaks things. I have seen it fail while in the middle of a change, leaving me with a table with the wrong name and a system halted.

    In some cases, this human review is really usefull: for example changing the datatype of one column would make SSMS drop and recreate the table, but it is perfectly fine to do this by using an ALTER TABLE command (not to mention the time required to do this if the table is big).

    Because I came from the databases world, I usually advice people to review the code that SSMS generates and then execute it – don’t trust blindly in SSMS nice little buttons 😉

  2. Pingback: SQL: Management Studio not saving changes to table | The Lonely DBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s