SQL Server 2008: Dumb New Default Setting in SSMS 2008

In an effort to protect DBAs and developers from themselves, Microsoft has introduced a new default setting in SQL Server Management Studio. This setting prevents you from saving almost any change to db tables and throws out the following error:

Saving changes is not permitted. The changes that 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.

I don’t know about you, but I stopped reading at the second sentence because most Microsoft error messages do not contain super helpful information. In this case, however, they provide the solution, which is to uncheck “Prevent saving changes that require the table to be re-created.” This setting can be found in Tools -> Options -> Designers -> Table and Database Designers.

Don’t make this change if you work with db metadata.

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *