存储过程中的表值参数获取执行权限被拒绝错误

I get the following error when calling a stored procedure that has a table valued parameter as one of the parameters

调用具有表值参数作为参数之一的存储过程时，我收到以下错误

The EXECUTE permission was denied on the object 'ValidationErrors'

对象'ValidationErrors'上的EXECUTE权限被拒绝

ValidationErrors is a TVP created with the following statement:

ValidationErrors是使用以下语句创建的TVP：

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

The user executing the stored procedure has execute privileges on the stored procedure. However, I still get the above error. Any ideas?

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

3 个解决方案

#1

I think you may also need to grant the user permissions to the type.

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

References for GRANTing permissions to types:
SQL 2005
SQL 2008

GRANTing对类型的权限的引用：SQL 2005 SQL 2008

Update:
Re: why you have to grant permissions on the type when you have permissions on the sproc. I don't know the definitive reason, but BOL says:

更新：Re：当您拥有对sproc的权限时，为什么必须为该类型授予权限。我不知道确切的原因，但BOL说：

Unlike user-defined types created by using sp_addtype, the public database role is not automatically granted REFERENCES permission on types that are created by using CREATE TYPE. This permission must be granted separately.

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

Update 2: To GRANT EXECUTE permissions, you'd run this in SSMS:

更新2：要获得GRANT EXECUTE权限，您可以在SSMS中运行它：

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

#2

As @chiefbrownbotom says, the reason why you need execute permissions on the table type is that the table is created before (and therefore outside of) the call to the proc. To illustrate this run a SQL Profiler trace and call your proc. You will see something like this which might surprise you...

正如@chiefbrownbotom所说，您需要对表类型执行权限的原因是该表是在对proc的调用之前（因此在其之外）创建的。为了说明这一点，运行SQL事件探查器跟踪并调用您的proc。你会看到这样的东西，这可能让你感到惊讶......

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

#3

Grant Control on TYPE::schema.mytabletype to RoleOrMember

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

This worked for me; many thanks to @chiefbrownbotom in the comment above.

这对我有用;非常感谢@chiefbrownbotom在上面的评论中。

#1