在SQL Server中防止删除表

时间:2020-12-02 03:41:12

Is there a way to prevent DROP TABLE in SQL Server somehow, simply by using SSMS and its features?

是否有一种方法可以通过使用SSMS及其特性来防止SQL Server中的DROP表?

2 个解决方案

#1


7  

Don't give users permissions to drop tables.

不要给用户删除表的权限。

You might think a DDL trigger can prevent this. It does, in a way: it lets the drop happen, then it rolls it back. Which is not quite preventing it, but I suppose it might be good enough.

您可能认为DDL触发器可以防止这种情况发生。从某种意义上说,它确实会这样做:它让水滴发生,然后把它滚回去。这并不能完全阻止它,但我想它可能已经足够好了。

#2


0  

Check this , There are two methods basically

检查一下,基本上有两种方法

The first one is based on creating a view on the table with option SCHEMABINDING. When the SCHEMABINDING option is used the table cannot be modified in a way that will affect the view definition, as well as it cannot be dropped unless the view is dropped first.

第一个是基于在表上创建带有选项SCHEMABINDING的视图。使用SCHEMABINDING选项时,不能以影响视图定义的方式修改表,也不能删除表,除非先删除视图。

The second method is using the new DDL triggers in SQL Server 2005. Defining a trigger for DROP_TABLE with rollback in the body will not allow dropping tables.

第二种方法是在SQL Server 2005中使用新的DDL触发器。在主体中使用回滚定义DROP_TABLE的触发器将不允许删除表。

#1


7  

Don't give users permissions to drop tables.

不要给用户删除表的权限。

You might think a DDL trigger can prevent this. It does, in a way: it lets the drop happen, then it rolls it back. Which is not quite preventing it, but I suppose it might be good enough.

您可能认为DDL触发器可以防止这种情况发生。从某种意义上说,它确实会这样做:它让水滴发生,然后把它滚回去。这并不能完全阻止它,但我想它可能已经足够好了。

#2


0  

Check this , There are two methods basically

检查一下,基本上有两种方法

The first one is based on creating a view on the table with option SCHEMABINDING. When the SCHEMABINDING option is used the table cannot be modified in a way that will affect the view definition, as well as it cannot be dropped unless the view is dropped first.

第一个是基于在表上创建带有选项SCHEMABINDING的视图。使用SCHEMABINDING选项时,不能以影响视图定义的方式修改表,也不能删除表,除非先删除视图。

The second method is using the new DDL triggers in SQL Server 2005. Defining a trigger for DROP_TABLE with rollback in the body will not allow dropping tables.

第二种方法是在SQL Server 2005中使用新的DDL触发器。在主体中使用回滚定义DROP_TABLE的触发器将不允许删除表。