存储过程中的表值参数出现执行权限被拒绝的错误。

Question

当调用一个存储过程并将表值参数作为其中一个参数时，我遇到了以下错误:

对象'ValidationErrors'上的执行权限被拒绝。

ValidationErrors是通过以下语句创建的TVP:

CREATE TYPE [dbo].[ValidationErrors] AS TABLE(
    [ErrorMessage] [varchar](255) NOT NULL
)

执行存储过程的用户具有存储过程的执行权限，但我仍然遇到上述错误。有任何想法吗？

- arunpereira

Answer 1

我认为您可能还需要授予用户对该类型的权限。

授予权限参考：
SQL 2005
SQL 2008 更新：
关于为什么在拥有存储过程权限的情况下仍需授权类型，我不确定确切的原因，但BOL表示：

与使用 sp_addtype 创建的用户定义类型不同， 公共数据库角色没有自动获得使用 CREATE TYPE 创建的类型上 REFERENCES 权限。 必须单独授予此权限。

更新2： 要授予执行权限，请在 SSMS 中运行以下命令：

GRANT EXECUTE ON TYPE::dbo.ValidationErrors TO SomeUser;

- AdaTheDev

---

我已经授予该对象“控制”权限，现在似乎一切正常。仅仅添加“引用”和“查看定义”并没有起到帮助作用。 就个人而言，我不理解这个要求，因为它是一个类型，通常当存储过程执行被授予时，被引用的对象不需要授予特定的权限。 然而，这可能与用户处于“公共”角色有关。 有什么想法吗？ - arunpereira

你是否尝试授予EXECUTE权限（而不是CONTROL）？在诉诸CONTROL之前，这个方法值得一试。 - AdaTheDev

我在SQL管理工具中没有看到授予执行权限的地方，只看到了我上面列出的那些选项。我需要使用T-SQL来授予执行权限吗？ - arunpereira

@chiefbrownbotom - 是的，我已经更新了我的答案，请查看更新2。 - AdaTheDev

存储过程可以避免在物理表或视图上设置权限的需要，但是 TVP 不仅仅是在存储过程中使用，而且还要传递给存储过程。换句话说，在调用 SP 之前，需要先创建 TVP 实例；因此，仍然需要授予 TVP 本身的权限。 - Aaronaught

通常我们会授予[public]表类型的EXECUTE和VIEW DEFINITION权限，除非你真的不想让陌生人看到那个整数表！ - Peter Radocchia

Answer 2

正如@chiefbrownbotom所说，你需要在表类型上拥有执行权限的原因是该表在存储过程调用之前（也就是在外部）创建。为了说明这一点，请运行SQL Profiler跟踪并调用您的存储过程。您会看到以下内容，可能会让您感到惊讶...

DECLARE @p1 TABLE AS YourTableType
INSERT INTO @p1 (col1, col2, col3) VALUES ('val1','val2','val3')
INSERT INTO @p1 (col1, col2, col3) VALUES ('val1','val2','val3')
...
EXEC usp_YourStoredProc @p1

- Ben Seaman

Answer 3

将 TYPE::schema.mytabletype 的控制权授予 RoleOrMember

这对我很有帮助；非常感谢上面评论中的 @chiefbrownbotom。

- Steve G