Enable The Option Prevent Saving Changes

Saving changes is not permitted in SQL Server Management Studio error

Past:   |   Updated: 2022-09-19   |   Comments (i)   |   Related: > SQL Server Management Studio

Problem

When working in SQL Server Direction Studio (SSMS) I got a alarm message while trying to save changes to a tabular array. The message was “saving changes is not permitted”. After the alert message the changes that I fabricated were rolled back. I take the proper permissions to implement such DDL operations on that tabular array, and so how can I control this restriction and what are the pros and cons associated with this permission?

Solution

The message in question is shown below and I got this message when I tried to change the “Let Nulls” setting for column [GroupName] of <HumanResources.Department> table of AdventureWorks database the post-obit warning was generated.

Saving changes is not permitted. The changes you have made require the post-obit tables to be dropped and re-created. Y’all have either made changes to a tabular array that can’t be re-created or enabled the pick Forbid saving changes that crave the table to be re-created.

I have proper permissions for the DDL statements on this table and in that location are no locks on the tabular array that should prevent information technology to be recreated. So the only other reason could exist based on the part of the message “or enabled the option Forestall Saving changes that require the tabular array to be re-created“.

So let’s get to this option then we will go through a list of some scenarios when table re-creation is required and likewise the blazon of potential threats related to making such changes through SSMS

  • From the menus select “Tools” and and so “Options…”

Accessing the Option in SSMS  to prevent changes that require table re-creation

  • Click “Designers” tab in left panel of frame

Option in SSMS  to prevent changes that require table re-creation

The marked choice in a higher place is the i that when checked prevents any change in SSMS that requires tabular array re-creation. By default this pick is checked. Y’all may uncheck it to allow yous to make any changes through SSMS that require tabular array re-creation. In one case this choice is un-checked, you will not even get a warning message for changes that require tabular array re-cosmos and your changes will be implemented.

Some of scenarios in which tabular array re-cosmos is required are

  • Modifying information type of a column
  • Inserting a cavalcade any where earlier final column of a table
  • Modifying a computed column expression of a computed column
  • Modifying the persistence property of a computed column
  • Modifying the identity holding of a column
  • Modifying a Aught property of a cavalcade
  • Re ordering of columns in a table

Of import Notation

It is of import to understand that there may be consequences that are associated with making such changes through SSMS. Microsoft strongly recommends to not turn off this selection. You may feel loss of some data associated with that table or fifty-fifty loss of data in certain conditions. As an example of loss of associated information Microsoft Support mentions change rail data associated with the table if the change track feature for a tabular array is enabled. Also if the tabular array holds a big corporeality of information then re-creation of table may lead to an functioning time out and information technology may non complete.

Be aware that a new table will created, the data moved to the new table and the old table volition be dropped.

Here is a uncomplicated example of a tabular array named “Table_1” that has 1 column “visitor”. Nosotros will add together a new column named “address” and put this before the “company” column.

The beneath script is generated from SSMS:

  • A new table “Tmp_Table_1” is created with the correct columns and lodge.
  • The data from “Table_1” is moved to “Tmp_Table_1”.
  • Tabular array “Table_1” is dropped.
  • Table “Tmp_Table_1” is renamed to “Table_1”.
/* To prevent any potential information loss issues, y'all should review this script in particular before running it exterior the context of the database designer.*/ Brainstorm TRANSACTION Set QUOTED_IDENTIFIER ON Prepare ARITHABORT ON SET NUMERIC_ROUNDABORT OFF Fix CONCAT_NULL_YIELDS_NULL ON Set ANSI_NULLS ON Set ANSI_PADDING ON Set up ANSI_WARNINGS ON COMMIT Begin TRANSACTION Go CREATE TABLE dbo.Tmp_Table_1 	( 	address varchar(50) NULL, 	company varchar(50) NULL 	)  ON [PRIMARY] GO ALTER TABLE dbo.Tmp_Table_1 Set (LOCK_ESCALATION = TABLE) Get IF EXISTS(SELECT * FROM dbo.Table_1) 	 EXEC('INSERT INTO dbo.Tmp_Table_1 (company) 		SELECT company FROM dbo.Table_1 WITH (HOLDLOCK TABLOCKX)') Become Drib Tabular array dbo.Table_1 Go EXECUTE sp_rename North'dbo.Tmp_Table_1', N'Table_1', 'OBJECT'  Get COMMIT
          

Next Steps

Keeping in mind the recommendation by Microsoft is to keep this pick enabled, but in that location may be some conditions where you may uncheck the option to easily work with the SSMS designers. These conditions may exist:

  • Yous are working in test surround
  • Some operations are required that are non possible though T-SQL. For example inserting a new column in center of other columns. In such cases properly analyze the table for any issues/loss as result of tabular array re-creation.
  • Y’all are sure that there is no associated data like alter track information associated with any of your tables
  • Yous are sure that hardware is quite capable to avoid any time out operations

Related Articles

Pop Manufactures

Almost the writer

MSSQLTips author Atif Shehzad
Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips

Article Last Updated: 2022-09-19

Source: https://www.mssqltips.com/sqlservertip/1740/error-saving-changes-is-not-permitted-in-sql-server-management-studio/