This SQL Server warning gets me every time I start work on a new SQL Server instance. This week I started generating Fact tables for a SharePoint Business Intelligence project. I am parsing thousands of SharePoint InfoPath forms and using the data to drive a SQL Server data warehouse that will initially exposed with Excel and SQL Server Reporting Services.
This warning is what stops developers from inadvertently deleting data when they make a table change that requires the table to be dropped and recreated. This can happen if you simply want to change a column’s data type. If you have no data in the table SQL Server will not complain. If you do have data that will be lost during the table recreate you will see this warning:
The fix is simple as long as you realize you are opening the door to deleting data. Select the Tools -> Options menu item in SQL Server Manager Studio. Select Designers node on the left in the Options dialog and uncheck Prevent saving changes that require table re-creation.
This is a designer option and will affect any connected databases. Unchecking this option will allow SQL Server drop tables but if you have data in the table it will still warn the user of lost data but you will have an option to continue.
When you get a Saving Changes is not Permitted warning it is not difficult to fix. The real reason I posted this is to remind myself where to find the check box in SQL Server Manager Studio.