SSMS: Saving changes is not permitted error

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.

Goto Tools->Options

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

One thought on “SSMS: Saving changes is not permitted error”

Leave a Reply to urbanprn Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s