Site hosting news, tutorials, tips, How Tos and more

How to work around an annoying SQL Management Studio Designer error

howto

This may be an old topic but it’s worth mentioning.

Ever since SQL 2005 was released, there is a weird setting in SQL Management Studio that prevents you from saving the changes on your database.  The error is very misleading because it gives an impression that the security error is coming from the SQL Server itself, when in fact it’s your SQL Management Studio that is generating the error.

I was hoping this would be fixed in SQL 2012 Management Studio, but it still seems to exist.

This is the error you will see in Management Studio:

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.

What will lead to this error is when you create a table, save it, then go back into the table and impose changes such as setting auto increment on a specific column.

The reason is because these types of changes will actually cause Management Studio to delete the table and recreate it.  This is done behind the scenes by SQL Management Studio.  So I guess SQL Management Studio wants to make sure it has permission before making such life altering changes.

Odd right? Fortunately the fix is really easy.  In SQL Management Studio (it does not matter whether you are connected to a database or not) go to Tools/Options/Designers/Table and Database Designers.  Make sure you uncheck “Prevent saving changes that require table re-creation.”

Now keep in mind, I’m using SQL 2012 Management Studio, but I believe this should be the same for the older SQL Management Studios, such as 2005 and 2008.  And as you can see, since I do not need to connect to the SQL Server to make this change, the setting is associated with Management Studio, not the SQL Server itself.


2 Responses
  • David Walker Reply

    I think this setting has been around since SQL Server 2005 Management Studio but I can confirm that it is definitely in the same place as you described in SQL Server 2008 Management Studio.

  • Ray Reply

    You’re right, this setting has been around since SQL 2005.

Leave a Reply to Ray Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.