Wednesday, February 3, 2010

Saving changes is not permitted (SQL Server 2008)

On clean installed SQL Server 2008 instance if you change the Table you may see this message:

This dialog states: "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created."

This can be fixed by changing settings for SQL Server Management Studio.
Open: Tools > Options

Do uncheck "Prevent saving changes that require table re-creation".
Once you do that save will work.

I think Microsoft should made it unchecked by default.


Sergey Volegov said...

Does table get repopulated with data after re-creating change?
If not, why should it be by default?

Vaghinak Petrosyan said...

Yes it does with no data loss. Your changes will be rejected otherwise.

e.g. adding new column (not allow null) to the table that contains some data will fail.
'Service-Identity-Users' table
- Unable to modify table.
Cannot insert the value NULL into column 'newcolumn', table 'dbo.Tmp_Service-Identity-Users'; column does not allow nulls. INSERT fails.
The statement has been terminated.

petsona said...

very helpful, made use of it :)