Saving changes not permitted MSSQL 2008

by 6:52 AM 3 comments
We recently upgraded a few SQL server boxes to MS SQL Server 2008. I also installed the new Enterprise Admin tools and after working on a new project I hit a slight brick wall.

"Saving changes not permitted" 

This kept coming up when I tried to change column attributes and re-arrange column orders for a table that existed.



It was odd I could make schema changes like this with SQL 2005 why not 2008?

Turns out its a simple setting that's on by default in the SQL 2008 Enterprise manager ( Express and Full ) and João Fernandes gave me the solution over twitter only a few mintues after I asked.

Here are the exact steps:

Click "Tools" , Select "Options", Expand "Designers", select "Table and Database Designers" then deselect "Prevent saving changes that require table re-creation"


 
  
 

Paul Kukiel

Developer

Cras justo odio, dapibus ac facilisis in, egestas eget quam. Curabitur blandit tempus porttitor. Vivamus sagittis lacus vel augue laoreet rutrum faucibus dolor auctor.

3 comments:

  1. Actually, the behavior, is actually a bug. Microsoft doesn't recommend unchecking that box and using T-SQL instead. I find that ridiculous however as a schema/db designer. We use the visual tools to save us time. I unchecked the box too :) The KB article acknowledging the bug was posted in March 2009, still no sign of a fix :( http://support.microsoft.com/kb/956176

    ReplyDelete
  2. I agree. Our DB upgrades are scripted but in development we use the Visual tools. I didn't know it was officially a bug I can imagine alot of people have been frustrated with this issue.

    ReplyDelete
  3. Thanks a bunch! This helped me out

    ReplyDelete