Saturday, May 19, 2012

Enabling Table Changes on SQL Server Management Studio


This has probably happened to you several times already, and you will always need to enable table modification the first time you use the SQL Server Management Studio.

The issue is that by default, SQL Server Management Studio doesn’t allow you to save changes on SQL tables that require the table to be dropped and re-created. This is a security feature that prevents table changes, which might be useful for production or staging environments, but definitely annoying for development environments (local or remote).

If you try to save a table change and the table recreation is not being allowed, you will get the following error:


To allow table changes to be saved, you need to modify the table options on the SQL Server Management Studio designers. To do so, open the Options window by clicking on Tools/Options.


Then select the Designers section from the left panel and uncheck the “Prevent saving changes that require table re-creation”, as shown in the following screenshot.


Once you click OK, the changes will take place and you will be able to save the table changes you do through the SQL Server Management Studio designer views. Just make sure that this is not allowed on the production environment !