How grant db_owner permission to some objects

  • Hi all,

    How can I grant one user to have db_owner permission on some tables , but only have db_datareader permission on others tables.

    Thanks

  • Judy (1/25/2008)


    Hi all,

    How can I grant one user to have db_owner permission on some tables , but only have db_datareader permission on others tables.

    Thanks

    db_owner is a database role and basically means you have all database level options. You can not give it to specific objects with in a database. Sounds like you need a custom database role that gives permissions appropriately to different objects.

  • Actually I created one custom database role, named "db_Custom" and added objects to this new role.

    Users under this role only have "select, update, delete,insert" on objects.

    But users need to own "alter table (view)" on those specific objects and create new table permission.

    How can I implement this task?

  • Add the role, ddladmin, to your custom_role.

  • Judy (1/25/2008)


    Actually I created one custom database role, named "db_Custom" and added objects to this new role.

    Users under this role only have "select, update, delete,insert" on objects.

    But users need to own "alter table (view)" on those specific objects and create new table permission.

    How can I implement this task?

    Check out "GRANT" in books online (http://msdn2.microsoft.com/en-us/library/ms188371.aspx)

    GRANT CREATE TABLE TO db_Custom - etc.

  • SQL ORACLE (1/25/2008)


    Add the role, ddladmin, to your custom_role.

    if I add ddladmin to custom_role, then users under this role will have ddladmin permission to all the tables, not only specific tables

  • Cant you set up the objects that this User needs to alter in a seperate schema and gove Alter Schema permission for that schema?

    -Roy

  • I can issue "Grant Create Table to db_custom", no problem. but users only can create new table and no permission to alter tables.

    When I exec "Grant Alter Table to db_custom", got the error message,

    Msg 165, Level 16, State 2, Line 1

    Privilege ALTER TABLE may not be granted or revoked.

    By the way, the objects' owner is dbo.

  • Roy Ernest (1/25/2008)


    Cant you set up the objects that this User needs to alter in a seperate schema and gove Alter Schema permission for that schema?

    I am using SQL Server 2000 SP4.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply