Question

当调用一个存储过程时，如果一个参数是表值参数，则会出现以下错误。

EXECUTE> >在标的上被拒批

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?

Answer 1

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

References for GRANTing permissions to types:
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:

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.

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

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

Answer 2

@chiefbrownbotom说,你为什么需要在表格类型上执行许可,原因是表格是在(因此不在)向原方发出呼吁之前制作的。为说明这一活动,请您介绍。你们会看到这样的情况,这可能会使你们感到惊讶。

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

Answer 3

授予角色或成员对TYPE::schema.mytabletype的控制权限。

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

友情链接