How to work around an annoying SQL Management Studio Designer error

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:

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.