Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there a way to deny drop permissions for a specific table from a user or role?

share|improve this question
up vote 3 down vote accepted

If you need to prevent to drop the table by some user, try this:

DENY DELETE ON OBJECT::dbo.table_to_deny TO restricted_user;

share|improve this answer
If this user has a db_owner role, is does not seem to affect the users ability to drop this table. – jrara Mar 30 '12 at 11:35
If you want to restrict the DBO user right, ask about it. Your question is "Is there a way to deny drop permissions for a specific table from a user or role?". Concerning your comment: prevent DBO user from deleting object in database - bad idea, You just MUST NOT give this user DBO rights. Give him appropriate rights for appropriate objects, but not DBO. DBO is owner of ENTIRE DATABASE. – Alex_L Mar 30 '12 at 12:37
I'm talking about db_owner role, not dbo user, those are different things. – jrara Mar 31 '12 at 12:22
I'm accepting this answer because it answer my question partly. – jrara Mar 31 '12 at 12:26
db_owner == dbo user. You should not be giving this privilege to any users. That is all there is to it. – user1240 Mar 31 '12 at 23:00

Try using the DDL trigger FOR DROP_TABLE:

PRINT 'Dropping tables are not allowed'
share|improve this answer
+1. Nice solution to the problem. Of course we prefer not to provide dbo rights to users in the first place :-) – user1240 Mar 31 '12 at 5:10
@RandolphWest true. The number of times I have heard "Just give dbo, so we don't have to worry about permissions". With SQL 2012, you can even fine tune the server role permissions. – StanleyJohns Mar 31 '12 at 11:55
Ok, this prevents drop table for the whole database, not just for one table. – jrara Mar 31 '12 at 12:25

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.