Starting Sql Server stores proc in the context of the caller

Question

It makes me crazy, and I'm sure the answer is so simple.

I have several schemas, each of which has a view called "Task". I want to make one saved process that can be executed by users running in several default schemes - which stores proc makes a choice in the task view.

So to speak, I have these objects:

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

StoredProc: dbo.MyProc - all users have execute permissions. Prok is simple:

select count(*) from Task 

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

 exec dbo.MyProc 

Then they will get a row count from the form fr.Task. But instead, they get the error "Invalid task object name."

Is it not possible to create a common storeproc file that will make the selection in the current user’s schema?

thanks bill

Answer 1

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

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

Answer 2

Use dynamic SQL, i.e., exec ('select count (*) from Task')