Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Error Saving changes is not permitted. in SQL Server 2008 Management Studio

MSSQLTips author Atif Shehzad By:   |   Read Comments (1)   |   Related Tips: More > SQL Server Management Studio

Problem
Working in SQL Server Management Studio (SSMS) 2008, I got a warning message while trying to save changes to a table "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?

Solution
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.

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.

Error message in SSMS  about prevent changes that require table re-creation

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..."

Accessing the Option in SSMS  to prevent changes that require table re-creation

  • Click "Designers" tab in left panel of frame

Option in SSMS  to prevent changes that require table re-creation

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.

Next Steps

Keeping in view the recommendations by Microsoft it is better to keep this option checked. But there may be some conditions where you may uncheck the option to easily work with 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

For response of Microsoft support team on this issue visit the link http://support.microsoft.com/kb/956176/en-us



Last Update: 4/29/2009


About the author
MSSQLTips author Atif Shehzad
Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Wednesday, April 29, 2009 - 10:05:13 AM - timothyrcullen Read The Tip

Good tip!  I was having that issue last night but didn't have the chance to look it up.  Thanks!



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.