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.
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..."
Click "Designers" tab in left panel of frame
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.
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