Saving changes is not permitted in SQL Server Management Studio error
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?
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
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
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
About the author
View all my tips
Article Last Updated: 2022-09-19