执行权限被拒绝在用户定义的表类型？ [英] The EXECUTE permission is denied on the user-defined table types?

问题描述

我有一个关于用户定义的表类型在SQL Server 2008 的。

有关我们SQL Server 2008上定义了我们自己的表类型的需要ASP.NET应用程序之一，在ASP.NET应用程序中执行SQL命令时使用它们作为存储过程的参数（我们通过DataTable对象作为参数存储过程看到这里的一个例子）

For the need of one of the ASP.NET application we defined our own table-types on SQL Server 2008 to use them as parameters in the stored procedures (when executing sql command in ASP.NET application we pass DataTable object as parameter for stored procedure see here for an example)

问题是，当我们从ASP.NET运行SQL命令（执行存储过程），我们得到一个错误：

The problem is that when we run Sql command (execute stored procedure) from ASP.NET we get an error:

执行权限被拒绝的对象'ourTableType'，
  数据库'ourDatabase'模式'ourSchema。

The EXECUTE permission was denied on the object 'ourTableType', database 'ourDatabase', schema 'ourSchema'.

为什么会这样？为什么我们需要设置用户自定义表类型的权限？为什么是不够的，有权限的设置只是使用它的存储过程？如果我们要设置它，不管是什么，为什么没有执行权限类型在属性窗口中设置任何（我只能看到控制，参考，取得所有权，视图定义）？

Why is that so? Why do we need to set permission on user-defined table types? Why is not enough to have permission set just on stored procedure that uses it? And if we have to set it no matter what, why there is no EXECUTE permission type to set in properties window whatsoever (I can see only Control, References, Take Ownership, View Definition)?

我也搞不懂的是，在属性窗口中设置权限控制解决了这个问题，并且存储过程没有问题运行。

What I also don't understand is that setting permission to Control in properties window solves the problem and the stored procedure runs without problems.

推荐答案

我真的希望你现在解决了这个，看到的问题是近4个月大，但如果你没有，这就是我想就是答案。

I really hope you've solved this by now, seeing as the question is almost 4 months old, but in case you haven't, here's what I think is the answer.

GRANT EXEC ON TYPE::[schema].[typename] TO [User]
GO

这篇关于执行权限被拒绝在用户定义的表类型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！