SSMS “Saving Changes Is Not Permitted”: The Two Ways Out, and Why the Guard Exists

You changed a column in the Table Designer, pressed Save, and SSMS refused. Nothing is broken and nothing has been lost yet. The dialog is a guard, and it fired because the change you asked for cannot be applied to the table you have. It can only be delivered by building a new table, copying every row into it, dropping the original and renaming the copy.

Almost every answer you will find tells you to untick a checkbox. That works, and it is the wrong first move. Clearing the box does not make the re-creation cheaper or safer. It only stops SSMS telling you one is about to happen.

Which Changes Trigger It

SSMS blocks the save for five designer changes, listed on the MS Docs troubleshooting page for this error. Four of the five have a one-line T-SQL equivalent that re-creates nothing:

The last row is worth sitting with. Reordering columns is one of the most common reasons people meet this dialog, and it is the one where the re-creation buys nothing at all. There is no statement for it because physical column order is not something anything should depend on. Name your columns in the SELECT list, or put a view in front of the table.

Way Out 1: Write It As ALTER TABLE

Open a new query window against the same database and run the statement. The syntax for every variant is on the MS Docs ALTER TABLE page. Two things to know before you do.

Give the complete column definition, not just the part you are changing. ALTER COLUMN replaces the whole definition rather than patching it, so leaving NOT NULL off a column that had it makes that column nullable as a side effect. Check what the column currently is before you overwrite it.

Going from NULL to NOT NULL fails if any row holds a NULL. Fix the data first, then alter the column.

-- Relax a column to allow NULLs. Metadata only, no row is touched.
ALTER TABLE dbo.Orders ALTER COLUMN Notes varchar(50) NULL;

-- Widen a column. int to bigint rewrites every row, so size it once and leave it.
ALTER TABLE dbo.Orders ALTER COLUMN Qty bigint NOT NULL;

-- Check what you have before you replace it.
SELECT c.name, t.name AS data_type, c.max_length, c.precision, c.scale, c.is_nullable
FROM sys.columns c
JOIN sys.types t ON t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.Orders')
ORDER BY c.column_id;

What The Designer Was About To Do

You do not have to guess. In the designer, Table Designer > Generate Change Script hands you the script before anything runs. It has the same shape every time: build Tmp_<table>, copy the rows into it under an exclusive table lock, drop the original, rename the copy, then put the primary key and the indexes back. All of it inside one transaction.

BEGIN TRANSACTION;

CREATE TABLE dbo.Tmp_Orders
(
    OrderId     int NOT NULL IDENTITY (1, 1),
    CustomerId  int NOT NULL,
    OrderDate   datetime2(0) NOT NULL,
    Qty         bigint NOT NULL,          -- the one change you asked for
    Notes       varchar(50) NOT NULL
) ON [PRIMARY];

ALTER TABLE dbo.Tmp_Orders SET (LOCK_ESCALATION = TABLE);
SET IDENTITY_INSERT dbo.Tmp_Orders ON;

IF EXISTS(SELECT * FROM dbo.Orders)
    EXEC('INSERT INTO dbo.Tmp_Orders (OrderId, CustomerId, OrderDate, Qty, Notes)
        SELECT OrderId, CustomerId, OrderDate, CONVERT(bigint, Qty), Notes
        FROM dbo.Orders WITH (HOLDLOCK TABLOCKX)');

SET IDENTITY_INSERT dbo.Tmp_Orders OFF;

DROP TABLE dbo.Orders;
EXECUTE sp_rename N'dbo.Tmp_Orders', N'Orders', 'OBJECT';

ALTER TABLE dbo.Orders ADD CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderId);
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId ON dbo.Orders (CustomerId);

COMMIT TRANSACTION;

Read the HOLDLOCK TABLOCKX hint again. That is an exclusive lock on the whole source table, taken at the start of the copy and held until the final COMMIT. Running the same pattern in a lab and reading sys.dm_tran_locks after the copy shows exactly that: request mode X on Orders and Sch-M on Tmp_Orders. The same change written as ALTER TABLE ALTER COLUMN shows one row, Sch-M on Orders.

Both block other sessions. The difference is duration. ALTER TABLE holds its Sch-M lock for one statement. The designer holds an exclusive lock on the table for as long as it takes to copy every row, plus the drop, the rename and every index rebuild after it.

What It Costs, Measured

A table of 300,000 rows at 13.9 MB on SQL Server 2025, simple recovery. Each operation ran in its own explicit transaction, with the log cost read from sys.dm_tran_database_transactions before the commit:

Two readings come out of that, and only one is the one people expect.

For a metadata-only change the gap is not close. Relaxing a column to allow NULLs cost 660 bytes of log in 8 log records, because no row was touched. The designer would have copied all 300,000 rows to deliver the same result, around 62,000 times the log for the same outcome. That is the case where the guard is doing you an enormous favour.

For a genuine type change, ALTER TABLE is not the cheaper option, and it is worth being straight about that. Taking Qty from int to bigint in place logged 53,098,948 bytes, more than the 41,343,848 the copy and rename logged, because the in-place change rewrites every row in the clustered index while the copy writes a fresh heap. If log volume alone decided it, the designer would win that one. It does not decide it, because the copy figure above stops at the rename: the primary key and every nonclustered index still have to be rebuilt on top of it, and the object itself does not survive. The table ends up with a new object_id, and anything bound to the old one goes with it.

What is deliberately not in that table is elapsed time. This lab shares a box with other work and spent most of the session under memory pressure, so a stopwatch there measures the box and not the operation. Log volume and lock mode are deterministic, so those are what is quoted.

What The Re-Creation Takes With It

This is the real reason the guard ships switched on, and MS Docs names it directly: the existing change tracking information is deleted when the table is re-created. Change tracking is bound to the object, and the designer’s script drops the object.

With change tracking enabled on the database and on the table, run this before and after:

SELECT OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) AS tracked_table,
       begin_version, min_valid_version, cleanup_version
FROM sys.change_tracking_tables;

Before the re-creation it returns one row: dbo.Orders, with begin_version 0, min_valid_version 0 and cleanup_version NULL. The designer script then runs, and the same query afterwards returns 0 rows.

No row, no error, no warning. The table is still there with all 300,000 rows in it, and every consumer that was polling CHANGETABLE for changes now has nothing to poll. The first anyone hears about it is a failed sync. Change Data Capture goes the same way for the same reason, its capture instance is tied to the object, though that half is documented rather than reproduced here.

So check before you change. Get CDC and Change Tracking tells you whether either is on, and How to Enable Change Data Capture is what you would be rebuilding afterwards.

Way Out 2: Turning The Guard Off

If you have read this far and still want the checkbox, here is where it lives:

1. In SSMS, open Tools > Options.
2. In the navigation pane, select Designers, then Table and Database Designers.
3. Clear Prevent saving changes that require table re-creation, and select OK.

SSMS 22 moved these settings into the new Unified Settings experience, same content and a new home, which is covered in What’s New in SSMS 22 for DBAs. The same page also carries the designer’s own transaction time-out override, which is the setting that kills a long re-creation part way through.

When is clearing it reasonable? A small table on a development instance, with no change tracking, no CDC, no replication, and nobody else connected. That is the list. MS Docs is unusually blunt about the rest of it: we strongly recommend that you do not work around this problem by turning off the option. On anything that matters, the statement in a query window is quicker to type than the trip through Options.

Frequently Asked Questions

Related

• SSMS: The Complete Guide, the hub this post belongs to
• How to Add Columns to Tables in SQL Server, the ALTER TABLE route the designer should have sent you down
• What’s New in SSMS 22 for DBAs, including where the options moved to
• How to Enable Change Data Capture (CDC) in SQL Server, the state a re-creation destroys
• DBA Scripts: Get Schema Change History, for finding out who changed the table and when

Summary

The dialog is SSMS telling you your change needs the table dropped and rebuilt. Write it as ALTER TABLE in a query window instead. For a nullability change that is 660 bytes of log against 41,343,848 for the re-creation, and for a type change it is not about the log at all, it is that the object survives with its indexes, its permissions and the change tracking state that a re-creation deletes without a word. The checkbox under Tools, Options, Designers removes the message, not the re-creation.