SQL Server 2008 Management Studio – Saving changes is not permitted error.


We’ve recently upgraded our development laptops with SQL 2008 from SQL 2005. Today I was trying to create a new database for a client within the management studio and every time I decided, for example, to change a column that was defined nullable to not nullable and tried saving my design, which in turn would save the table changes, it gave me the following error:

Now, I understand the need for potentially re-creating the table if for example you had an existing table where a column was nullable and it contained data then you wanted to make it not nullable (without a default value) it would correctly so moan. But you would think that the Microsoft devs would build a system intuitive enough to know that where there isn’t any data in the table yet, because you’re designing the schema, it could automatically drop and re-create the table and any associated relationships.

Needless to say, to prevent this from happening to you – go into the Tools -> Options and click on the Designers item in the tree menu on the left and make sure “Prevent saving changes that require table re-creation” is not ticked as below:

Hope that helps someone else too!

Advertisements

53 Responses to “SQL Server 2008 Management Studio – Saving changes is not permitted error.”

  1. love the wtf! I have a feeling that the error message doesnt make sense, and is in a circular loop. i love the authorative “saving changes is not permitted.” imagine if this happened to word docs (for example)! how different everything would be! does this encourage perfectionism first time round?

    next question – why is there a checkbox with “prevent saving changes …” ? i’ve not worked with sql, hence this might sound blonde, but it seems somewhat ludicrous to me!

  2. thanks for posting the solution! i tend to click cancel, walk away, and have a cuppa. if computer says no, i say wtf (with attitude and snapping fingers).

  3. @S – yeah if you only had one chance to save your document we’d either have a lot of perfectionists or no documents at all!

    Actually it’s not preventing saving – but preventing saving where your changes would require SQL Server to drop and re-create the table – which becomes a problem if your table already contains data 🙂

  4. aaahhhh thank you!!! i was literally having to go back to SSMS 2005 to make changes to tables on our 2005 server, which was annoying. you are forever owed my gratitude.

  5. @Paul – you’re very welcome – glad it helped someone else too.

  6. Steven Benjamin Says:

    Thank you for taking the time to post this!
    Not only have you saved me a boat load of wasted time,
    At least now I know that I am not the only one who thinks that MS SQLSMS has once again been designed by people who have never managed a DB before.

    • @Steven – you’re more than welcome – as a software developer I know all too well how frustrating these things can be and believe in sharing knowledge. Glad I could help and that it helps others too (it seems to be a popular post from my blog statistics). Yeah I’ve often wondered about some of the MS software developers.

  7. Thanks, this helped me..

  8. Thanks, just what I was looking for!

    The Internet is great! 🙂

  9. Richard Zeien Says:

    This helped me out too. What a pain.

  10. Thank you, this has bugged me for some time!

  11. Thanks a bunch!

  12. Thanks – that worked perfectly!

  13. Probably saved me hours of frustration.

    Thanks!

    • @Herb – you’re welcome. I wish Microsoft had never implemented that ‘feature’ since it’s actually useless and doesn’t do anything useful but irritate users. It’s pointless in the fact that what it is attempting to prevent, doesn’t actually happen as I’ve updated schemas by changing column data-types and adding new columns on tables with large amounts of rows without the need for it to recreate that table. I’m sure one of the SQL developers thought they were clever (sneaky) and it somehow got overlooked in the QA process.

  14. Many thanks for this, you saved me from smashing something 😉 What a joke ….

  15. Thank you so much…I just love the way M$ changes things and gives NO clue

  16. Thanks, this solution was the first on google. I didnt think about looking at the options menu.. was searching the problem in permissions and such..

  17. Thanks, it helped me out!

  18. Christian G Says:

    Very Very Thanks!!!!

  19. Thanks ! the screenshot is helpful!

  20. Thanks for the info, man! This error was driving me crazy!

  21. thanks a lot dude

  22. thanks who made this very simple instructions. i wish all instructions will be like this as simple as it is. hehehe

  23. it’s really help. Thanks a lot.

  24. Thank you!

  25. many thanks it work for me

  26. Thanks!

  27. Helped me. Thanks.

  28. ty, this was really starting to bug me

  29. Thanks for documenting the solution.

  30. Thank you very much. Your solution saved me a ton of work.

  31. well…what happens if you want to add a column to an existing table with exitsing data in the other fields already? drop and recreate? Hmmm sounds not an option to me!

  32. Just made to upgrade and ran into the same problem. Thanks for the help.

  33. Thx you Very much 🙂

  34. Thnx. even in 2012 it helps!

  35. gr8 info.. thnx

  36. […] Solution: To disable this AWESOME safety feature, Go to Tools –> Options –> and select Designers.  Uncheck “Prevent saving changes that require table re-creation”.  Bam!  Problem solved!  Here is the blog I found this on: https://deems.wordpress.com/2008/10/20/sql-server-2008-management-studio-saving-changes-is-not-permit… […]

  37. Thank you! Thank you! Thank you. Dumbest default I’ve encountered to date…

  38. thank you. wonderful job

  39. Thank you, this solved my problem.

  40. Thank you guys, solved my issue

  41. Thank you so much! I didn’t know whether to laugh or to cry when I read Microsoft’s preventing me from saving my schema, knowing that MS applications practically tell me “please save everything regularly, because this application can crash at anytime”.

  42. This solved my problem too.

  43. thanks…….

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

%d bloggers like this: