How grant db_owner permission to some objects

Question

How grant db_owner permission to some objects

Judy Why

Hall of Fame

Points: 3656
More actions
January 25, 2008 at 9:37 am

#119726

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

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

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

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.

Judy Why Hall of Fame Points: 3656 More actions · Answer 2

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?

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

SQL ORACLE

One Orange Chip

Points: 27807

January 25, 2008 at 10:28 am

#771882

Add the role, ddladmin, to your custom_role.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 4

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.

Judy Why Hall of Fame Points: 3656 More actions · Answer 5

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 6

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

Judy Why Hall of Fame Points: 3656 More actions · Answer 7

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.

Judy Why Hall of Fame Points: 3656 More actions · Answer 8

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.