SQL 2008 - "Saving changes is not permitted." error message..

Tuesday, September 23, 2008

Hi! I came across with this error in SQL Server Management Studio everytime I made some modifications in a table, i.e. adding relationships.

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.

It was pretty tedious and generated a lot of doubts to me. Then, after making a quick search I found this great post from Brian Knight which explains how to avoid this behavior 'by design' :)

In a nutshell, the solution is:

This is by design and can be quickly fixed in Management Studio by unchecking a property. To fix this in Management Studio, go to Tools -> Options then go to the Designer Page and uncheck "Prevent saving changes that require table re-creation".

Complete info, here: http://pragmaticworks.com/community/blogs/brianknight/archive/2008/06/04/sql-server-2008-designer-behavior-change-saving-changes-not-permitted.aspx

57 Comments

Good One! Worked. Thank you

Santosh Rane - Saturday, December 6, 2008 9:51:45 AM

I try to fixed it very long times.Thanks a lot. ^_^

skyline - Monday, December 8, 2008 2:54:23 AM

Thanks worked out well. I'm still learning SQL so am grateful when finding time savers like this.

Mike Hankey - Sunday, January 4, 2009 10:42:10 PM

That's silly. "Unhandy by design" ?
When I'm designing tables, most of the time they'll still be _empty_ anyway, so what do I care if they need to be dropped and rebuilt...

Eoan - Wednesday, February 4, 2009 1:11:22 PM

...that is the behavior by default! ..lol!

pablop - Wednesday, February 4, 2009 1:43:16 PM

Same people who designed Vista obviously worked on SQL 2008

Bill Kuhn - Tuesday, March 10, 2009 4:49:30 PM

What a bunch of idiots, by default we don't let you modify a table after it has been created, MUPPETS!!

Tim Mather - Tuesday, March 31, 2009 12:38:32 PM

Thanks a lot. Now i am able to do the chages in the table poperties uisng management studio.

Rajesh.T - Saturday, June 20, 2009 9:30:37 AM

Wow now even in SQL 2008 they make these silly "we know what's best for you" decisions. I fear in SQL2010 MS Bob will reappear. MSFT what's wrong with you lately ?

nojetlag - Friday, July 31, 2009 6:02:55 PM

I think this is a GOOD thing, as you wouldn't want to drop a table in a production db by accident. My nit is that SSMS tries to drop the table even if I simply add a new column to the table.

Guarnarius - Tuesday, December 29, 2009 3:24:59 PM

отлично написано, у автора прям талант

kikus - Sunday, June 13, 2010 1:15:16 PM

Thank you so much!)

HSE student - Friday, December 24, 2010 2:34:27 PM

Yes !!!! many thanks mister ^^

fof - Monday, January 17, 2011 7:21:13 PM

Thanks for a very good and simple solution

Daniel Morkla - Friday, February 4, 2011 4:16:39 PM

Thanks - you saved me a lot of work!

Esther Pressler - Saturday, February 12, 2011 1:01:21 AM

Thank you very much

Vahe - Thursday, March 10, 2011 8:02:20 AM

thanks good solution. i am trying to save i found your way

london hotels - Friday, April 1, 2011 3:35:44 PM

Thanks alot, I was bout to pull out my hair. Such an easy fix, IF you know what button to Push.
Thanks,
Earl

Earl - Sunday, April 3, 2011 11:54:42 PM

If you use this option, SQL might just wipe out all the existing data in your table. That happened to me when I added a datetime field set to 'no nulls' to an existing table. It did not give me a warning about what it was going to do either.

Dave - Tuesday, April 5, 2011 2:08:23 PM

thanks a ton. worked for me

jp - Friday, May 13, 2011 3:56:29 PM

thanks . its working

naqvi - Tuesday, May 17, 2011 6:39:28 AM

Thanks for the tip!

ross - Wednesday, May 25, 2011 2:28:44 PM

Crazy thing to have check marked out of the box without notifying DBA's or Developers...
Thanks for being there.

DHS_DBA - Friday, July 1, 2011 8:27:33 PM

Great! it's work, thank you very much!!!

chani123 - Wednesday, July 13, 2011 9:18:27 AM

Great ! It's work. thanx....

Siddheshwar Duchal - Friday, August 19, 2011 12:46:57 PM

this helped.thanks

bobby - Tuesday, September 13, 2011 2:52:55 PM

this hint should be in the error message ...

thanks!!!!

mkk - Tuesday, October 4, 2011 7:33:33 PM

Really timely help great pakka

Jegannathan - Wednesday, October 19, 2011 5:25:11 PM

Smart help, Thanks A lot

Vinay Kr Chowdhary - Saturday, November 19, 2011 3:29:58 AM

Cheers for this - just saved me a load of time

Mass - Friday, November 25, 2011 10:39:58 AM

I feel dumb for not knowing this. Thanks! You saved me from myself!

Buck - Saturday, January 21, 2012 9:52:36 PM

More stupid default behaviour from MS to remove! Thanks for a great find :)

Sequel Swerver - Thursday, January 26, 2012 2:42:24 PM

Thaks a lot,
Your post helped me solve my issue. Thanks

Amit Patel - Wednesday, February 1, 2012 9:20:48 PM

Saved me lots of frustrations Thanks

Fred - Tuesday, March 27, 2012 9:42:34 PM

Hi,

Thanks great help as my time saved...

All the Best

Chandrashekhar M (CM) - Tuesday, April 10, 2012 4:46:44 PM

Thanks! Works great :)

Dan Bailey - Saturday, April 14, 2012 11:33:15 PM

God Bless you, thanks

adeel - Thursday, April 19, 2012 9:41:03 AM

Thank you so much!!!

JR - Monday, May 14, 2012 1:50:53 AM

Saved me lots of time

Thank you very much!!!

JR - Monday, May 14, 2012 2:05:23 AM

Thankz bro.. Help me alot.. 1000(Y)

Mohomad - Monday, May 14, 2012 5:09:30 PM

Thank you ... Good job

Murugavel - Wednesday, May 16, 2012 12:54:57 AM

Very Good, I love you

Milad - Monday, June 11, 2012 7:42:57 AM

Thank God I foud this link......I was sunk!!

Babji2012 - Monday, July 23, 2012 7:56:18 PM

Thank you very much, I have been trying to fix this few days. I just want to add MSS 2008 R2 steps:

Tools > Options > Designers > Table and Database Designers then uncheck Prevent saving changes that require table re-creation

Abdul.Bowwe - Monday, July 23, 2012 8:26:29 PM

Thank you @Abdul for sharing this for MSS 2008 r2.!

pablop - Monday, July 23, 2012 8:41:06 PM

thanks a lot it worked ..

vijeth - Friday, August 24, 2012 5:51:44 AM

thanks saved my time

anu - Tuesday, September 18, 2012 2:15:32 PM

Thanks a lot.I used it

Amir - Sunday, October 14, 2012 7:49:00 PM

Thanks alot that was very helpful

ako - Thursday, October 25, 2012 3:04:05 AM

it solved my problem T_T thank you

michael - Monday, November 5, 2012 9:42:12 AM

OMG thank yo so much!

Farah - Thursday, November 15, 2012 12:13:55 AM

Thank you very much

Haitham - Wednesday, November 21, 2012 9:59:25 AM

Thanks a lot! I was on the way to suicide because of this damned error. Now it works! Thank you again! :-)

Alex K - Friday, November 23, 2012 9:07:04 AM

thankxx... it was very quick n easy...

mrunmayee - Wednesday, December 26, 2012 2:42:16 PM

Awesome! Thank you...

Judirick - Wednesday, January 30, 2013 11:03:49 AM

Thanks

It worked for me. i was having this problem for long time. very easy

Hetal - Friday, February 15, 2013 1:07:05 AM

Thanks.. It works well.

ZT - Friday, July 5, 2013 5:18:03 PM

Comments have been disabled for this content.