---
title: "How to Prevent to Deny drop permission of a table in SQL Server?"  
description: "How to Prevent to Deny drop permission of a table in SQL Server?"  
author: "Steilla Mitchel"  
published: 2021-10-21  
updated: 2021-10-21  
canonical: https://www.mindstick.com/forum/156792/how-to-prevent-to-deny-drop-permission-of-a-table-in-sql-server  
category: "mssql server"  
tags: ["sql server", "sql", "sql-server-2016"]  
reading_time: 2 minutes  

---

# How to Prevent to Deny drop permission of a table in SQL Server?

How to Prevent to Deny [drop](https://www.mindstick.com/forum/159392/how-to-fetch-the-value-of-the-selected-options-in-drop-down-in-php) [permission](https://www.mindstick.com/forum/159434/linux-service-permission-denied-error-adjust-permissions) of a [table](https://www.mindstick.com/articles/43918/how-to-design-table-using-bootstrap) for all [Users](https://www.mindstick.com/news/2244/issue-preventing-users-from-accessing-facebook-s-social-networking-platforms-has-been-resolved) in [SQL Server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server)?

## Replies

### Reply by Steilla Mitchel

## Deny Drop Permission for a Table:

Sometimes cases are generated in that users drop the table from database without informing or by mistake dropped any table then If you want to prevent to drop the table by some user, you can use the following [SQL](https://www.mindstick.com/articles/13115/types-of-keys-in-sql-or-oracle-database) statement ,

```
DENY DELETE ON OBJECT::SchemaName.TableName TO RistrictedUser;
```

The other Deny permission for a user then the following SQL statement is used. First deny the SELECT statement for a particular user.

```
DENY SELECT ON OBJECT::SchemaName.TableName TO RistrictedUser;
```

You also used the following SQL statement for deny the UPDATE table statement for a particular user.

```
DENY UPDATE ON OBJECT::SchemaName.TableName TO RistrictedUser;
```

The above SQL statement is used to Deny the drop permission on a particular user.

If you want to restrict on all the user at a time to deny drop permission of all table in your database then you have need to create a DDL Trigger in your database which stop the drop permission of tables. The following SQL statement is used to create a trigger on database to deny the permission,

```
CREATE TRIGGER TR_DropTable
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
 BEGIN TRAN
 PRINT 'Drop Table are not allowed!!!'
 ROLLBACK TRAN;
END
```

\


---

Original Source: https://www.mindstick.com/forum/156792/how-to-prevent-to-deny-drop-permission-of-a-table-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.