locked
SQL Server 2008 R2 Express Edition Management Studio: Table Design Modification RRS feed

  • Question

  • Hello,

    I'm trying to modify a table using Management Studio (SQL Server 2008 R2 Express Edition). Like DataType/Column Name/Add or Delete a colums.

    While I want save table it shows an error stating that T-SQL can not be executed and suggested that I need permission or to drop the table and recreate the same.

    The same can be done by executing T-SQL statement from new Query window.

    Anyway I want to Use GUI to modify the table as I'm not familier to write code frequently.

    Myeen Chowdhury.

     

    Tuesday, October 11, 2011 9:40 AM

Answers

  • When you change the table structure using SSMS, SSMS drops and re-creates the table,

    When you change it using TSQL, the existing structure is modified..

     

    You will need permissions to DROP the object if you want to change it using SSMS.

    SQL Server – ALTER COLUMN – Management Studio v. T-SQL


    - Vishal

    SqlAndMe.com

    • Marked as answer by Peja Tao Tuesday, October 25, 2011 7:22 AM
    Tuesday, October 11, 2011 10:28 AM
  • Hi Mayeen,


    I think you need to connect your SQL administrator to grant you some permission. Drop table requires ALTER permission on the schema to which the table belongs, CONTROL permission on the table, or membership in the db_ddladmin fixed database role.


    Hope this helps.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Peja Tao Tuesday, October 25, 2011 7:22 AM
    Thursday, October 13, 2011 2:40 AM

All replies

  • When you change the table structure using SSMS, SSMS drops and re-creates the table,

    When you change it using TSQL, the existing structure is modified..

     

    You will need permissions to DROP the object if you want to change it using SSMS.

    SQL Server – ALTER COLUMN – Management Studio v. T-SQL


    - Vishal

    SqlAndMe.com

    • Marked as answer by Peja Tao Tuesday, October 25, 2011 7:22 AM
    Tuesday, October 11, 2011 10:28 AM
  • Hi Mayeen,


    I think you need to connect your SQL administrator to grant you some permission. Drop table requires ALTER permission on the schema to which the table belongs, CONTROL permission on the table, or membership in the db_ddladmin fixed database role.


    Hope this helps.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Peja Tao Tuesday, October 25, 2011 7:22 AM
    Thursday, October 13, 2011 2:40 AM
  • Did you find a solution?

    I created tables but I cannot alter them in SQL Express/VS interface and my MgtStudio will not connect ot sqlexpress. I cannot script the tables so I have to rebuild them from scratch with all datatypes just because I decided to make a change that would require a table rebuild? Alter table doesn't work either. I guess my windows auth user may not be full admin but it was my install and I have full rights to machine albeit not domain admin and I logon domain/myloginame. 

    Monday, November 28, 2011 10:17 PM