Restricting rights over some tables

[e106199]

Hi all,
i am trying to create a database user who has access to a database but not full access. I want him to be able to write (create new tables, stored procedures, views etc) but i want to restrict his rights over some tables. If i have 10 tables i want him to access 6 of them and not be able to touch the rest 4 or anything that is using one of these 4 tables. If there is a view that uses on of these restricted tables i want it to be not accessible to the user also, same for stored procedures etc.

i set the user as teh db ovner and restricted his rights over a table and he cant read the contents of teh table but can still change its design. And restricting his rights over that table didnt stop the user to see the contents of a view that uses this table.

any ideas how this could be done?
thank you
-shane

 

[yelworcm]

You should be able to use

deny select, insert, update, delete on table from user

Not sure off hand if it is deny FROM, or deny TO. BOL will be the final authority, of course.

Giving the user rights to create his own tables will be a bit stickier admin-wise. You can grant him "create table", but he will only be able to create tables under hsi own username. In SQL Server 2005 this problem is aleviated some, but I still shudder at multi-owner databases.

 

[e106199]

Hi,
thanks for the answer but what i ma looking for is much more complex. I can grant him select/insert/update/delete rights with no problem BUT i want him to be able to create new tables (1) I want him to be able to design his granted tables (2) i want him not to be able to desing the tables he s not granted access (3)

So basically, he should be able to (a) create new tables (b) modify his tables (c)not touch restricted tables

thank you
-shane

 

[mrdenny]

You can grant the ability to create objects.

GRANT CREATE TABLE TO UserName
GRANT CREATE PROCEDURE TO UserName

I'm not sure if you can grant ALTER TABLE rights to a specific table such as

GRANT ALTER TABLE on TABLE1 TO UserName

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[e106199]

Hi,
thank you for the valuable contribution. Now the user with the public rights assigned can create new tables or procedures. But the alter statement is incorrect. I looked at the Grant syntax and there is no alter. Any idea how Alter table is granted to a user?
-shane

 

[mrdenny]

As I said, I wasn't sure if it was going to work. I didn't think it was going to. I don't think a user can alter a table unless they own the table (or they are a member of the ddl_admin role or the db_owner role.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[e106199]

Hi,
there is gotta be a way. Here is what i m thinking: if its possible to have 2 users(owner) for a table i ll probably get what i need. Is this possible?
Or is it possible to deny "design table" option for a user over a table?
thank you
-shane

 

[mrdenny]

No, only one use can own an object.

I've you've granted a user to ddl_admin or db_owner rights there isn't a way to remove rights from specific objects within the database.

I'm assuming that this is a dev server. Why so locked down on the objects?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com