如何在 SQL Server 中从截断中保存我的表

问题描述

我需要限制我的表格 update 和 delete.

I need to restrict my table from update and delete.

对于 Update 和 Delete 我使用的是 Instead of 触发器，并且它工作正常.

For Update and Delete I'm Using an Instead of trigger, and its working fine.

但是如果我使用 TRUNCATE 命令，它会删除我的数据.

But if I use the TRUNCATE Command, it erases my data.

我需要限制 truncate 或任何其他表修改过程.

I need to restrict truncate or any other table modification process.

谁能给您宝贵的建议.

推荐答案

要运行 TRUNCATE 语句，您需要对对象具有 ALTER 权限；根据文档:TRUNCATE TABLE (Transact-SQL) - 权限.

To run a TRUNCATE statement you need to have the ALTER permissions on the object; as per the documentation: TRUNCATE TABLE (Transact-SQL) - Permissions.

如果要阻止用户/角色在表上使用 TRUNCATE 语句，则需要使用以下内容(替换大括号中的文本 ({})):

If you want to stop a user/role from using the TRUNCATE statement on a table, you will need to use the following (Replacing the text in braces ({})):

USE {YourDatabase}
DENY ALTER ON {YourTable} TO {User/Role};

您可以使用快速测试脚本轻松测试这是否有效:

You can easily test if this works with a quick test script:

USE Sandbox;
GO

CREATE TABLE dbo.TestTable (Id int IDENTITY(1,1), String varchar(10));

INSERT INTO dbo.TestTable (String)
VALUES ('asdfhj'),('asdjkas'),('asdjkhsad');
GO

CREATE USER TestUser WITHOUT LOGIN;

ALTER ROLE db_ddladmin ADD MEMBER TestUser;
ALTER ROLE db_datareader ADD MEMBER TestUser;
ALTER ROLE db_datawriter ADD MEMBER TestUser;
GO

EXECUTE AS USER = 'TestUser';
GO
TRUNCATE TABLE dbo.TestTable; --This works
GO
REVERT;
GO

SELECT *
FROM dbo.TestTable;

INSERT INTO dbo.TestTable (String)
VALUES ('asdfhj'),('asdjkas'),('asdjkhsad');

DENY ALTER ON dbo.TestTable TO TestUser;
GO

EXECUTE AS USER = 'TestUser';
GO
TRUNCATE TABLE dbo.TestTable; --This fails
GO
REVERT;
GO

SELECT *
FROM dbo.TestTable;
GO
--Clean up
DROP TABLE dbo.TestTable;
DROP USER TestUser;

这篇关于如何在 SQL Server 中从截断中保存我的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！