locked
Permission to change the design of a table RRS feed

  • Question

  • Hi,

      We are having Sql server 2008 in clustered environment. I logged in and tried to change the design of the table. I have

    the rights as a database owner for my login. But when I tried to change the design of the table in SSMS I got the following error-:

    Saving changes not permitted. The change you have made require the table to be dropped or recreated. You either

    made changes to a table that can't be recreated or enabled the option prevent saving changes that require table to

    be recreated.

    How to overcome the problem?

    Tuesday, January 14, 2014 10:57 AM

Answers

  • Hello Binny,

    That's not a SQL Server error message or permission setting; it's a "feature" of SSMS to prevent may unwanted changes. In SSMS click menu "Tools" => "Option ...", then "Designer" => "Table and Database Designer"; see screenshot below.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, January 14, 2014 11:24 AM
  • And you could still go ahead and do it using t-sql statement ALTER TABLE

    ALTER TABLE ADD col1 int,Col2 varchar(50),...

    GO ALTER TABLE ALTER COLUMN Col1 ....



    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, January 14, 2014 11:45 AM
  • You overcome the problem by staying out of the table designer. The reason that the roadblock is there is that the code that generates the table changes by creating a new instance of the table and copy data over has some very fundamental bugs and you should absolutely not use it. And least of all in a cluster, that is, in production! It's a toy tool, and Microsoft should have taken it out long ago. As just one example: the tool will only use ALTER TABLE in situations where it would work on SQL 6.5!

    So use SQL scripts to make your table changes. Preferably with ALTER TABLE. Writing scripts where you copy all data is doable, but it requires a good understanding of what can go wrong. Or a long maintenance window that permits you to restore a backup if things go south.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, January 14, 2014 10:12 PM

All replies

  • Hello Binny,

    That's not a SQL Server error message or permission setting; it's a "feature" of SSMS to prevent may unwanted changes. In SSMS click menu "Tools" => "Option ...", then "Designer" => "Table and Database Designer"; see screenshot below.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, January 14, 2014 11:24 AM
  • And you could still go ahead and do it using t-sql statement ALTER TABLE

    ALTER TABLE ADD col1 int,Col2 varchar(50),...

    GO ALTER TABLE ALTER COLUMN Col1 ....



    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, January 14, 2014 11:45 AM
  • You overcome the problem by staying out of the table designer. The reason that the roadblock is there is that the code that generates the table changes by creating a new instance of the table and copy data over has some very fundamental bugs and you should absolutely not use it. And least of all in a cluster, that is, in production! It's a toy tool, and Microsoft should have taken it out long ago. As just one example: the tool will only use ALTER TABLE in situations where it would work on SQL 6.5!

    So use SQL scripts to make your table changes. Preferably with ALTER TABLE. Writing scripts where you copy all data is doable, but it requires a good understanding of what can go wrong. Or a long maintenance window that permits you to restore a backup if things go south.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, January 14, 2014 10:12 PM