6

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

2 Answers 2

7

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;

http://msdn.microsoft.com/en-us/library/ms173724.aspx

4
  • 1
    If this user has a db_owner role, is does not seem to affect the users ability to drop this table.
    – jrara
    Commented Mar 30, 2012 at 11:35
  • 2
    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
    Commented Mar 30, 2012 at 12:37
  • I'm talking about db_owner role, not dbo user, those are different things.
    – jrara
    Commented Mar 31, 2012 at 12:22
  • db_owner == dbo user. You should not be giving this privilege to any users. That is all there is to it.
    – user1240
    Commented Mar 31, 2012 at 23:00
7

Try using the DDL trigger FOR DROP_TABLE:

CREATE TRIGGER NO_DROP_TABLE
ON DATABASE
FOR DROP_TABLE
AS
PRINT 'Dropping tables are not allowed'
ROLLBACK
4
  • +1. Nice solution to the problem. Of course we prefer not to provide dbo rights to users in the first place :-)
    – user1240
    Commented Mar 31, 2012 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. Commented Mar 31, 2012 at 11:55
  • Ok, this prevents drop table for the whole database, not just for one table.
    – jrara
    Commented Mar 31, 2012 at 12:25
  • @jrara, is a database trigger, so yes, in the whole database. Commented Aug 9, 2018 at 16:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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