在调用者的上下文中运行Sql Server存储过程

This is making me nuts and I'm sure the answer is SO easy.

这让我疯了，我确信答案很容易。

I have multiple schemas that each have a view named "Task". I want to make a single stored proc that users running in multiple default schemas can execute successfully -- that stored proc does a select on the Task view.

我有多个模式，每个模式都有一个名为“任务”的视图。我想创建一个单独的存储过程，运行在多个默认模式中的用户可以成功执行 - 存储过程在任务视图上执行选择。

So say I have these objects:

所以说我有这些对象：

View: fr.Task (users with default schema of 'fr' get this if they just type "select * from Task" View: de.Task (ditto, but for users with default schema of 'de')

查看：fr.Task（默认模式为'fr'的用户如果只是键入“select * from Task”，请查看：de.Task（同上，但对于默认模式为'de'的用户）

StoredProc: dbo.MyProc -- all users have execute permissions. The proc is simply:

StoredProc：dbo.MyProc - 所有用户都具有执行权限。 proc简单地说：

select count(*) from Task

Now I would expect (and I want) that if a user with default schema 'fr' did

现在我希望（并且我想）如果具有默认架构'fr'的用户做了

exec dbo.MyProc

Then they would get the count of rows from view fr.Task. But instead they get error "Invalid object name 'Task'."

然后他们将从视图fr.Task获得行数。但他们得到错误“无效的对象名称'任务'。”

Is it not possible to make a generic storedproc that will execute a select in the schema of the running user?

是否不可能生成将在正在运行的用户的模式中执行select的通用storedproc？

Thx, Bill

比尔，比尔

3 个解决方案

#1

To run a stored procedure in the context of the CALLER you can use the Execute As clause however, I suspect that this is not really what you are wanting to do.

要在CALLER的上下文中运行存储过程，您可以使用Execute As子句，但我怀疑这不是您想要做的事情。

http://msdn.microsoft.com/en-us/library/ms188354.aspx

#2

Use Dynamic SQL ie., exec ('select count(*) from Task')

使用动态SQL即，exec（'从任务'中选择计数（*））

#3

"Is it not possible to make a generic storedproc that will execute a select in the schema of the running user?" The answer is "NO"

“是不是可以创建一个通用的storedproc来执行正在运行的用户的模式中的select？”答案是不”

the default_schema is taken from the location of the stored_procedure

default_schema取自stored_procedure的位置

create procedure fr.MyProc as select * from Task
create procedure de.MyProc as select * from Task

will return in the first case the fr.Task-Table and in the second case the de.Task-Table. No matter, which default_schema the caller has

将在第一种情况下返回fr.Task-Table，在第二种情况下返回de.Task-Table。无论如何，调用者都有default_schema

#1