Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How grant db_owner permission to some objects Expand / Collapse
Author
Message
Posted Friday, January 25, 2008 9:37 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 6:27 AM
Points: 568, Visits: 813
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
Post #447626
Posted Friday, January 25, 2008 9:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:43 PM
Points: 1,147, Visits: 1,937
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.


---
SQLSlayer
Making SQL do what we want it to do.

Post #447639
Posted Friday, January 25, 2008 10:06 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 6:27 AM
Points: 568, Visits: 813
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?

Post #447642
Posted Friday, January 25, 2008 10:28 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 5, 2013 4:43 PM
Points: 1,473, Visits: 1,314
Add the role, ddladmin, to your custom_role.
Post #447662
Posted Friday, January 25, 2008 10:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:43 PM
Points: 1,147, Visits: 1,937
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.


---
SQLSlayer
Making SQL do what we want it to do.

Post #447663
Posted Friday, January 25, 2008 10:52 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 6:27 AM
Points: 568, Visits: 813
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
Post #447683
Posted Friday, January 25, 2008 11:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:31 AM
Points: 2,361, Visits: 6,749
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
Post #447696
Posted Friday, January 25, 2008 11:07 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 6:27 AM
Points: 568, Visits: 813
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.
Post #447697
Posted Friday, January 25, 2008 11:09 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 6:27 AM
Points: 568, Visits: 813
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.
Post #447702
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse