Deny permissions

Question

Post reply

Deny permissions

Tara-1044200

SSCoach

Points: 15951
More actions
September 9, 2010 at 1:28 pm

#226181

I deny permissions on some tables for certain users to update,alter,insert but i found that those users are denied to do so but they can drop the table compltely, how can i deny that.

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

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

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

Roy Ernest

SSC-Dedicated

Points: 38780

September 9, 2010 at 2:58 pm

#1218913

Are these users DDL Admin?

-Roy

jjtetzlo SSCrazy Points: 2511 More actions · Answer 2

What role do these users have?

Here's a relevant discussion from the msdn forums: http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/c562ef25-3157-4068-8957-9095e31b1ffc

If they're a member of any of the roles mentioned in that post, you'll have to modify these users to restrict them further.

- Jeff

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 3

If the users still own the table, or they have control permissions, they can remove the table.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

These users have R/W/ddl_admin previalges on the dataabase and so i am trying to deny those permission.

Is there a way i can restrict these users for dropping at table level but i still want them to be able to drop other tables which i am not concerned about.

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 5

You can set permissions by schema, or you could assign permissions by object (using a role).

Ultimately, if you have people with DDL_Admin and they are dropping table they shouldn't be, don't try to set permissions. Have a manager to stand behind them and chastise them.

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

I Agree with Steve. You do not give DDL_Admin privileges to people who are irresponsible. You will need to remove these people from DDL_Admin role and then give explicit permission to tables to a new database role and these users to that role.

-Roy

Tara-1044200 SSCoach Points: 15951 More actions · Answer 7

That is how the system is setup which can not be changed as they need liberty to drop and create tables other wise i will have to do that for them everytime for silly stuff.

ddl_admin previalge is acceptable for them but i have to restrict them for few tables, please help me to acheive that.

I dont see any DENY permisisons for DROP under table properties.

Ryan D. SSCommitted Points: 1595 More actions · Answer 8

Can you have them work on their own schema instead of the default dbo. That way they can drop there own tables and no longer need ddl rights. Of course this leads them to make sure that permission grants across the different schema tables are created.