Database.ca SQL Server Blog

My Blog

Wednesday, February 19 2014

Keywords: SSMS Error, SQL Server Management Studio Can not save table schema changes, error, Saving changes is not permitted

Can't Save Table Schema Changes in SQL Server Management Studio?

You are here because you have received the following error prompt, when you tried to save a table schema change using SQL Server Management Studio's Table Designer...making changes directly in Column Properties window.

 

What you must know is that the apparently benign looking little change you just made actually needed to performe the following actions behind the scene:
 

  • Drop all constraints in this table and all foreign key constraints to this table in the database
  • Rename old table to a temporary name
  • Recreate a new table with the new schema
  • Copy data from the old table to the new
  • Drop old table

 

As you see these could be RISKY as well as TIME CONSUMING actions.

 

SQL Server Management Studio by default, out of the box, does not allow the user to accidentally take such actions. You need to turn this feature on manually...this way you are consenting that you know what is going on and the potential impact of schema changes you could be making on a production database.

 

SSMS Error, SQL Server Management Studio Can not save table schema changes, error, Saving changes is not permitted

So when you do get this prompt...and you decide you want to be able to make table schema changes using your SQL Server Management Studio. Click Cancel to close the prompt.

 

SSMS Error, SQL Server Management Studio Can not save table schema changes, error, Saving changes is not permitted

You might receive yet another confusing prompt...just because the prior Save Dialog was cancelled out...OK this window to continue.

 

SSMS Error, SQL Server Management Studio Can not save table schema changes, error, Saving changes is not permitted

Tools-->Options

 

SSMS Error, SQL Server Management Studio Can not save table schema changes, error, Saving changes is not permitted

Designers-->Table and Database Designers
 
Uncheck "Preventing saving changes that require table re-creation"

 

 

 

Written by Ramin Haghighat