Error when saving a table in SQL Server: "Saving changes is not permitted"

Error Message
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.

Why?
This problem occurs when the Prevent saving changes that require the table re-creation option is enabled, and you make one or more of the following changes to the table:

• You change the Allow Nulls setting for a column.
  
• You reorder columns in the table.
  
• You change the column data type.
  
• You add a new column.
  

When you change a table so that you alter the metadata structure of the table, and then you save the table, the table must be re-created based on these changes. This may result in the loss of metadata and in a direct loss of data during the re-creation of the table.

Fixing the Problem

OPTION 1 - Using the ALTER SQL keyword to change the table definition.

Code Snippet

1. ALTER TABLE NameOfTable ALTER COLUMN ColumnToAlter INT NOT NULL

End of Code Snippet

Option 2 - Turning off the "Prevent saving changes that require table re-creation" option. NOT RECOMMENDED!. This may cause data loss due to changes with the meta structure of the table. A good example of potential data loss is if you enable Change Tracking.

If you only have the table structure set up and/or data loss isn't an issue (Test Data for example), then here are the following steps to turn off this data protection feature.

1. Open SQL Server Management Studio (SSMS).
2. On the Tools menu, click Options.
3. In the navigation pane of the Options window, click Designers.
4. Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.