SQL Server 2008 Error: Saving changes is not permitted
Posted by Dan Rigsby on September 26th, 2008
If you have been playing with SQL Server Management Studio 2008 and are modifying tables in the designer, you may run into this 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.
Here is a screenshot:
In the 2008 , they have added a new option to prevent saving of changes that will require a table re-creation. If you think about it, this is a good thing. If you are running a production database, you want maximum performance. If Management Studio needs to re-create a table to apply a change that means it needs to create a copy of the table, transfer the data, create the new table, move the data over, delete the old stuff, etc. This can potentially be a major operation.
However, if you don’t want this feature enabled or our just working with a development or test database, it is easy to turn off. Just open up the Options window in Management Studio and go to “Designers –> "Table and Database Designers”, find the “Prevent saving changes that require table re-creation” option, and turn it off.
September 27th, 2008 at 4:07 pm
[...] SQL Server 2008 Error: Saving changes is not permitted – Dan Rigsby This SQL Server thing is becoming more and more complex. At least when you don’t spend a lot of time in there [...]
October 6th, 2008 at 8:30 pm
Much appreciated Dan – saved me a ton of time.
October 12th, 2008 at 10:29 am
Thanks a lot!
October 19th, 2008 at 5:15 pm
[...] http://www.danrigsby.com/blog/index.php/2008/09/26/sql-server-2008-error-saving-changes-is-not-permi... [...]
November 14th, 2008 at 4:28 pm
Thanks.. ur answer solved the problem bang on !!
March 15th, 2009 at 1:41 pm
Needed to get around this, searched google and found you were second result
April 12th, 2009 at 8:25 am
Thank you very much! My headache has been cleared!
April 12th, 2009 at 11:39 pm
Thank you.
May 19th, 2009 at 9:13 am
Thanks a lot…helpful
May 28th, 2009 at 5:34 pm
Thanks so much!
June 24th, 2009 at 9:41 am
This didn’t work for me. It is supposed to and our DBA sent out an email telling us to do this same thing, however, he is out today of all days.
January 5th, 2010 at 5:18 pm
If I knew you, I’d buy you a beer.
January 25th, 2010 at 3:43 am
thanks Dan, save me a lot of time
February 17th, 2010 at 4:09 pm
Definitely top 10 dumbass new features
February 22nd, 2010 at 2:05 pm
Thank you Dan! for your help Much appreciated….
February 24th, 2010 at 8:18 am
Great article and thank you for that. I was going through the same problem and found this article very helpful.
One thing I noticed that, I could not find “Designers –> “Table and Database Designers” menu but I had to go through the Tools -> Options to get there.
Thank you,