Welcome to MSSQL Forum!
MSSQL Forum - Discussions about Microsoft SQL Server

You are currently viewing our community forums as a guest user. Sign up or
Having an account grants you additional privileges, such as creating and participating in discussions.

Error in SSMS on SQL Server 2008: "Saving changes is not permitted".

Discussion in 'Database Design and Database Objects' started by Peter Schmitz, Oct 1, 2012.

  1. Peter Schmitz

    Peter Schmitz Administrator Staff Member

    We all have to make changes to our tables every now and then. Columns might have to be added or removed, moved around within the table definition, made NULLABLE or not to accept NULLS, or perhaps the column definition originally built proves to be too conservative, and thus column lengths will have to be altered to allow for bigger values.

    Rather than doing this by scripting, personally I will use the table designer from SSMS, as that allows me to work much faster than having to build up the command manually (and seeing I do not believe in blindly memorizing SQL Scripting syntax, it also involves using BOL to be reminded of the exact syntaxes to be used).

    Whenever you use the UI Designer (which can be accessed by right-clicking the table and selecting 'Design', as shown in the screenshot below), there's a caveat, though.

    Design.png

    Any of the following changes require the table to be dropped and then recreated:​

    • Reordering columns
    • Adding a new column
    • Making changes to the "Allow NULL" setting of a column
    • Changing the data type of a column
    If you are working on a default installation, you will then see the following error:

    saving_changes_is_not_permitted.png


    To circumvent the error, from within SSMS head over to the top-menu, click Tools, and then select Options:​

    Menu_Tools_Options.png

    In the Options screen, select "Designers", and on that screen, uncheck the box next to "Prevent saving changes that require table re-creation":​

    options_designers.png

    That's it. The nasty error message should now no longer pop up.​

    One additional comment is that if you have "Change tracking" configured on the table in question, re-creating the table will wipe out whatever "Change tracking" has been stored so far.​

    If you do have "Change tracking" enabled, and cannot afford to lose this information, leave the option above as it was, and use T-SQL to update the tables.​

Share This Page

Sponsored link: