Tell me more ×
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. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question

2 Answers

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;

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

share|improve this answer
1  
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
1  
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. – Randolph West Mar 31 '12 at 23:00
up vote 5 down vote

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
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 :-) – Randolph West 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

 
discard

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.