Error Saving changes is not permitted in SQL Server Management Studio

Problem

When working in SQL Server Management Studio (SSMS) I got a warning message while trying to save changes to a table. The message was “saving changes is not permitted”. After the warning message the changes that I made were rolled back. I have the proper permissions to implement such DDL operations on that table, 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 “Allow Nulls” setting for column [GroupName] of <HumanResources.Department> table of AdventureWorks database the following warning was generated.

I have proper permissions for the DDL statements on this table and there are no locks on the table that should prevent it to be recreated. So the only other reason could be based on the part of the message “or enabled the option Prevent Saving changes that require the table to be re-created“.

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

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

• Click “Designers” tab in left panel of frame

The marked option above is the one that when checked prevents any change in SSMS that requires table re-creation. By default this option is checked. You may uncheck it to allow you to make any changes through SSMS that require table re-creation. Once this option is un-checked, you will not even get a warning message for changes that require table re-creation and your changes will be implemented.

Some of scenarios in which table re-creation is required are

• Modifying data type of a column
• Inserting a column any where before last column of a table
• Modifying a computed column expression of a computed column
• Modifying the persistence property of a computed column
• Modifying the identity property of a column
• Modifying a NULL property of a column
• Re ordering of columns in a table

Important Note

It is important to understand that there may be consequences that are associated with making such changes through SSMS. Microsoft strongly recommends to not turn off this option. You may experience loss of some information associated with that table or even loss of data in certain conditions. As an example of loss of associated information Microsoft Support mentions change track data associated with the table if the change track feature for a table is enabled. Also if the table holds a large amount of data then re-creation of table may lead to an operation time out and it may not complete.

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

Here is a simple example of a table named “Table_1” that has one column “company”. We will add a new column named “address” and put this before the “company” column.

The below script is generated from SSMS:

• A new table “Tmp_Table_1” is created with the correct columns and order.
• The data from “Table_1” is moved to “Tmp_Table_1”.
• Table “Table_1” is dropped.
• Table “Tmp_Table_1” is renamed to “Table_1”.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET 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)
GO
IF EXISTS(SELECT * FROM dbo.Table_1)
 EXEC('INSERT INTO dbo.Tmp_Table_1 (company)
SELECT company FROM dbo.Table_1 WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Table_1
GO
EXECUTE sp_rename N'dbo.Tmp_Table_1', N'Table_1', 'OBJECT' 
GO
COMMIT

Next Steps

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

• You are working in test environment
• Some operations are required that are not possible though T-SQL. For example inserting a new column in middle of other columns. In such cases properly analyze the table for any issues/loss as result of table re-creation.
• You are sure that there is no associated data like change track data associated with any of your tables
• You are sure that hardware is quite capable to avoid any time out operations