Saving Changes Is Not Permitted in SQL Server (SSMS Table Designer Fix)

Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. ...

That’s the SSMS Table Designer refusing to drop your table. Saving changes is not permitted isn’t an engine error, and your data is fine: nothing was saved.

Quick answer: make the change with ALTER TABLE in a query window. It edits the table in place, so no re-create happens:

ALTER TABLE dbo.Customers ALTER COLUMN Phone varchar(12) NULL;

What You Need Before You Start

Tested on SQL Server 2025 (17.0.1000.7) Developer with SSMS 21 and sqlcmd. You need ALTER permission. Here’s the table you’ll change; check a column’s current type any time:

CREATE TABLE dbo.Customers (
    CustomerID  int          NOT NULL CONSTRAINT PK_Customers PRIMARY KEY,
    FullName    nvarchar(20) NOT NULL,
    State       char(2)      NOT NULL,
    Phone       varchar(12)  NOT NULL,
    CreditLimit decimal(8,2) NOT NULL
);
INSERT dbo.Customers VALUES
 (101, N'Hannah Brooks',  'WA', '206-555-0141', 5000.00),
 (102, N'Tyler Reed',     'TX', '512-555-0187', 7500.00),
 (103, N'Grace Miller',   'CO', '303-555-0122', 3000.00),
 (104, N'Marcus Bennett', 'TX', '214-555-0190', 9000.00),
 (105, N'Olivia Hayes',   'IL', '312-555-0175', 4500.00);
ALTER TABLE dbo.Customers ENABLE CHANGE_TRACKING;

Why SSMS Blocks the Save: Changes That Re-create the Table

Why it happens: some edits can’t be done in place, so SSMS would copy the rows to a new table, drop the old one and rename the copy. A default-on option blocks that.

Designer change	Do this instead
Allow Nulls	ALTER COLUMN ... NULL
Data type or length	ALTER COLUMN col new_type
Add a column	ADD col type NULL (at the end)
Reorder columns	Order them in a view
Make a column identity	Add a new identity column

Fix 1: Run ALTER TABLE Instead of the Designer

The safe fix. Three designer edits as T-SQL, then sys.columns:

SET NOCOUNT ON;
-- Allow Nulls
ALTER TABLE dbo.Customers ALTER COLUMN Phone varchar(12) NULL;
-- data type
ALTER TABLE dbo.Customers ALTER COLUMN CreditLimit decimal(10,2) NOT NULL;
-- new column
ALTER TABLE dbo.Customers ADD Email varchar(30) NULL;

SELECT CAST(name AS varchar(11)) AS col,
       CAST(TYPE_NAME(user_type_id) AS varchar(8)) AS type,
       precision, is_nullable
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.Customers');

Confirm it’s fixed:

col         type     precision is_nullable
----------- -------- --------- -----------
CustomerID  int             10           0
FullName    nvarchar         0           0
State       char             0           0
Phone       varchar          0           1
CreditLimit decimal         10           0
Email       varchar          0           1

Phone shows is_nullable 1, CreditLimit has precision 10, and Email sits at the end. Defaults and NOT NULL columns are in SQL Server ADD COLUMN.

Fix 2 (Risky): Turn Off Prevent Saving Changes That Require Table Re-creation

Microsoft strongly recommends against it. For a dev box only:

1. In SSMS, select Tools > Options.
2. In the navigation pane of the Options window, select Designers.
3. Clear Prevent saving changes that require table re-creation and select OK.
4. Save, then turn the option back on; while it’s off, SSMS won’t warn you about rebuilds.

What Does Re-creating the Table Delete?

I ran an in-place ALTER, then a copy-drop-rename re-create:

SET NOCOUNT ON;
UPDATE dbo.Customers SET CreditLimit += 500 WHERE State = 'TX';
ALTER TABLE dbo.Customers ALTER COLUMN Phone varchar(12) NULL;
SELECT COUNT(*) AS after_alter
FROM CHANGETABLE(CHANGES dbo.Customers, 0) AS ct;
-- re-create: copy, drop, rename
SELECT * INTO dbo.Tmp_Customers FROM dbo.Customers;
DROP TABLE dbo.Customers;
EXEC sp_rename 'dbo.Tmp_Customers', 'Customers';
ALTER TABLE dbo.Customers ADD CONSTRAINT PK_Customers PRIMARY KEY (CustomerID);
ALTER TABLE dbo.Customers ENABLE CHANGE_TRACKING;
SELECT COUNT(*) AS after_recreate
FROM CHANGETABLE(CHANGES dbo.Customers, 0) AS ct;

Output:

after_alter
-----------
          2
Caution: Changing any part of an object name could break scripts and stored procedures.
after_recreate
--------------
             0

Two tracked updates survived the ALTER. After the re-create? Zero, even with tracking back on.

Common Mistakes to Avoid With ALTER COLUMN

T-SQL gives you real errors instead of a dialog. I hit these in one run:

SET NOCOUNT ON;
ALTER TABLE dbo.Customers ALTER COLUMN Phone varchar(12) NULL;
UPDATE dbo.Customers SET Phone = NULL WHERE CustomerID = 105;
BEGIN TRY  /* NOT NULL, but a NULL exists */
    ALTER TABLE dbo.Customers ALTER COLUMN Phone varchar(12) NOT NULL;
END TRY BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;
BEGIN TRY  /* too short for the data */
    ALTER TABLE dbo.Customers ALTER COLUMN FullName nvarchar(10) NOT NULL;
END TRY BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;
BEGIN TRY  /* identity on an existing column */
    EXEC (N'ALTER TABLE dbo.Customers ALTER COLUMN CustomerID int IDENTITY(1,1);');
END TRY BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;

The catch blocks printed:

Msg 515: Cannot insert the value NULL into column 'Phone', table 'DfSaveChgDemo.dbo.Customers'; column does not allow nulls. UPDATE fails.
Msg 2628: String or binary data would be truncated in table 'DfSaveChgDemo.dbo.Customers', column 'FullName'. Truncated value: ''.
Msg 156: Incorrect syntax near the keyword 'IDENTITY'.

• Msg 515: UPDATE the NULLs first. Oddly, it says UPDATE fails.
• Msg 2628: too short for the data (the truncated value came back empty). See string or binary data would be truncated.
• Msg 156: add a new identity column, then copy values in with IDENTITY_INSERT if you need them.
• Skipping NULL or NOT NULL: per the docs, the column turns nullable.

Best Approach: Script Every ALTER TABLE

Leave the option on and script table changes, or turn on Auto generate change scripts on the same page to see the SQL first. For column order, set it in a view.

Next Steps if the Save Still Fails

If it still fails with the option off, use ALTER TABLE. If ALTER fails, match the Msg number above.

More we’d suggest:

• On PostgreSQL? change a column’s data type in PostgreSQL
• Hit Msg 544 next? an explicit value for the identity column

Frequently Asked Questions