SQL SERVER – Deny Drop Permission for a Table

I have previously written a blog post about database triggers. Lots of people asked me to write a follow-up blog post where we can deny drop permission for a table from the SQL Server Security Standpoint. Let us explore today.

Here are the blog posts which will give you the context of the topic.

• Prevent Unauthorized Index Modifications – SQL in Sixty Seconds #183
• SQL SERVER – Prevent Users from Changing Index
• SQL SERVER – Trigger on Database to Prevent Table Creation
• SQL SERVER – Database Trigger for Index Can Prevent Index Rebuild

Well, in the previous blog posts we have learned that we can use database triggers to prevent the drop of the table. However, that may not be the best solution every single time. If you want to deny any user permission to drop any table, you can use the security role.

Here is how you can do it.

DENY DELETE ON OBJECT::SchemaName.TableName TO UsertoRestrict;

Well, that’s it for today. Let me know if you have any questions and I will be happy to answer them in the comments section.

Here are my few recent videos and I would like to know what is your feedback about them. Do not forget to subscribe SQL in Sixty Seconds series.

• MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
• Tuning Query Cost 100% – SQL in Sixty Seconds #181
• Queries Using Specific Index – SQL in Sixty Seconds #180
• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176

Reference: Pinal Dave (http://blog.SQLAuthority.com)