Saving changes is not permitted while modifying table design


Problem: There may be scenario while modifying the table design which needs to re-create the table. Even with the full permission while modifying it from SSMS, you get the below message and your table changes will be rolled back.

Saving changes is not permitted. The changes 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.

Solution:
Below are the few scenarios where changes in table design will re-create the table.

Changing the column data type
Changing the NULL property
Changing the order of the columns
Adding column before the last column
Changing the identity column property
Changing the computed column expression

Let’s see how can we fix the “Saving Changes is not permitted …”

Go to SSMS – Select Tools menu -> click on Options -> Go to Designers tab page -> Click on “table and database designers” page -> Un-check “Prevent Saving changes that require table re-creation” check box.

Advertisements

One thought on “Saving changes is not permitted while modifying table design

  1. Celesta

    Pretty section of content. I just stumbled upon your web site and in accession capital to assert that I acquire in fact enjoyed account your blog posts. Anyway I will be subscribing to your feeds and even I achievement you access consistently quickly.

    Reply

Thanks for the comment, will get back to you soon... Jugal Shah

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 )

Google+ photo

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

Connecting to %s