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.

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.

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

Exit mobile version