Saving Changes to Database Tables in SSMS

by Jan 25, 2017

Let say you just build a new SQL Server and, while designing some tables in a databases, you realized there are changes, or adding, to some fields as requirements will sometimes change.  So, when complete your tables field(s) changes, and when you save the changes, you ended up getting a popup error "Saving changes is not permitted. …".

If this was changes done to a production system, then this popup message is necessary in order to protect you from doing something wrong.  But, in most cases, we'll be doing this in our development environment.


Enable to save our changes

In order to bypass this popup message, we need to make a change in our SSMS (SQL Server Management Studio) settings under the "Option" menu.

Then, navigate to "Designer | Table and Database Designers" section and look for "Prevent saving changes that require table re-creation".  This is "Checked" by Default.

So, to allow us to save our changes to our table(s), we just need to "Unchecked" – "Prevent saving changes that require table re-creation". (see image below)

Now, changes to the table design can be saved and no pop message will be displayed.
Please, keep in mind. This setting change will only reside on the SSMS application. Typically, this will be on the SQL Administrator and/or Database Developer workstation (not on a production SQL Server).