仅通过存储过程强制 INSERT [英] Force INSERT only via stored procedure

问题描述

使用 SQL Server 2008，有没有办法只允许通过存储过程插入表，如果是，如何?

Using SQL Server 2008, is there a way to allow inserts to a table only via a stored procedure, and if so how?

最好的方法可能是 Martin Smith 建议使用 INSTEAD OF INSERT 触发.这个问题的直接答案是 marc_s 带有 GRANT 和 DENY 的答案，尽管它不会限制某些用户帐户.

The best way to go is probably Martin Smith's suggestion to use an INSTEAD OF INSERT trigger. The direct answer to this question is marc_s' one with GRANT and DENY, though it won't limit some user accounts.

推荐答案

不要授予任何数据库用户(以及您的公共"角色)对表的 INSERT 权限.

Just do not grant any database users (and your "public" role) the INSERT permission on the table.

授予这些用户执行 INSERT 存储过程的权限 - 这样，他们可以调用存储过程，但不能直接将任何数据插入到基础表中.

Grant those users the permission to execute the INSERT stored proc - that way, they can call the stored proc, but they cannot directly insert any data into the underlying table.

DENY INSERT ON dbo.YourTable TO PUBLIC
GRANT EXECUTE ON dbo.InsertDataProc TO PUBLIC

这篇关于仅通过存储过程强制 INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！