I was trying to change the datatype of a field in one of my tables using SSMS, and I received the Saving changes is not permitted error. Read below to see how I was able to make the change.
Below is a look at my table. Nice and basic only 2 columns.
Now I want to change the datatype of the Description field from varchar to nvarchar. We may be receiving data that cannot fit the varchar datatype in the future, so I need to make this change.
I attempt to change the datatype to nvarchar.
I click save and click Yes to confirm my change.
Then I get this alert saying I cannot make the change.
This is actually a good alert. If I was changing a datatype from nvarchar to varchar, there could be a loss of data since not all characters from a nvarchar field can be saved into a varchar field.
I do want to make this change, so I need to modify SSMS to allow it.
Then goto: Designers->Table and Database Designers. You can see that the Prevent saving changes that require table re-creation is checked.
Uncheck that option and click OK.
Now you can make the change and it will save for you.
To read more about this setting and reasons you may not want to change it, please follow this link: https://docs.microsoft.com/en-us/troubleshoot/sql/ssms/error-when-you-save-table