locked
Can't alter table in GUI RRS feed

  • Question

  • We've got a database developer that needs to have rights to modify a table in a development environment.  He currently belongs to these DB roles: db_datareader, db_datawriter, and db_ddladmin.  He is the user that created the table, which belongs to the dbo schema, and his default schema is the dbo schema.

    Whenever he writes t-sql to alter the columns in the table, everything works, but when he tries to right-click the table and choose 'design', he gets the following error:

    "Table dbo.ClinicalSolutionsExportAudit is set to read only, user doesn’t have enough rights on this table.”


    I was under the impression that these three db roles would give him the permissions he needed, but apparently I'm missing something.  Any ideas?  We are on SQL Server 2005, SP2, on Windows Server 2003.

    Thanks!
    Clint
    • Edited by NashVegas Tuesday, January 26, 2010 8:44 PM typo
    Tuesday, January 26, 2010 8:38 PM

Answers

  • Hi Nash,

     

    The problem here is the default schema being dbo.

    The dbo schema will not let the other users to ALTER or perform DDL changes as long as they are not in db_owners role at the database level.

     

    If you want to get past through this behaviour then you would have to define a new schema for your developers and that new schema should be owned by developer user . Now all the objects that developer creates will be of developerschema.objname instead of dbo.objname and hence giving him all access only on his own objects with db_ddladmin privilege.


    Otherwise you can stick on to giving your developer with explicit access to tables like ALTER TABLE privilege. Have a look here http://msdn.microsoft.com/en-us/library/aa337286.aspx

     


    Thanks, Leks
    Tuesday, January 26, 2010 9:03 PM