We had a table in our development database and they were trying to modify a table column. Suddenly they got the below error.
Since it was development server all had the access rights to drop,create and alter tables. So I was surprised why this error was popping up.
I went ahead with my approach to use query editor and was able to do the same change using ALTER TABLE..ALTER COLUMN syntax. This had me perplexed for a while as I didnt understand why table designer was not allowing the change to happen.
After some hours of research I found out the reason. The secret lies in one of the default settings within SQL Management Studio options shown below.
Go to Tools-> Options from top menu and navigate to Table and Database Designers settings coming under Designers and you’ll see the below
See the setting in blue which is what prevents saving any changes through designer which causes table to be recreated. Clear the setting and you would be able to do above type of object modifications through the designer, though I myself doesn’t prefer it to be done through designer.