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.

SQL SERVER - Deny Drop Permission for a Table DropPermission-800x409

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)

SQL Scripts, SQL Server, SQL Server Security, SQL Trigger
Previous Post
SQL SERVER – Check If a Column Exists 2016 Onwards
Next Post
SQL SERVER – Running SQL Agent Job After Completing Another Job

Related Posts

2 Comments. Leave new

  • Thank you for your clear steps.

    Could you also please get us a query to deny delete permissions on all the tables that we have

  • The above will deny delete on tables and also restrict dropping tables.


Leave a Reply