Call view from Stored Procedure

I have a user VIEW, let's call it [MIKE].Table wich does a filtered select SELECT * FROM TABLE WHERE TL_FILTERKEY in (1,2,3)

So, if Mike connects to the server and executes

"SELECT * FROM TABLE"

he will see only part of the data from table.

There is also a procedure with dbo rights and permision to Mike for execution as

CREATE PROCEDURE tbSelect
as
SELECT * FROM TABLE

If Mike executes exec tbSelect he will see ALL rows from Table, but not filtered开发者_如何转开发.

How to write the procedure so that it will select data from the user view(filtered rows)?

I tried recreate the procedure 'WITH EXECUTE AS 'Mike'' and 'WITH EXECUTE AS CALLER', however all rows are returned.

Your question is a little confusing, and you don't mention your SQL Server version, but I suspect that your problem is due to schemas. When you reference an object without a schema, SQL Server looks for an object in the user's default schema first, then the dbo schema.

As a complete guess, I think you have a user called Mike whose default schema is also Mike, a view called Mike.Table and a table called dbo.Table. When Mike runs his query Table is resolved to Mike.Table but when a db_owner queries Table it's resolved as dbo.Table.

To avoid confusion, you should probably use clearer object names (at least for testing) and always qualify object names with the owner:

create table dbo.TestTable (col1 int)
go

insert into dbo.TestTable 
select 1
union all
select 2
union all
select 3
go

-- this will return all rows
select col1 from dbo.TestTable
go

create view dbo.TestView
as
select col1
from dbo.TestTable
where col1 >= 2
go

-- this will return 2 rows
select col1 from dbo.TestView
go

create procedure dbo.TestProc
as
select col1 from dbo.TestView
go

-- this will also return 2 rows
exec dbo.TestProc
go

drop proc dbo.TestProc
go
drop View dbo.TestView
go
drop table dbo.TestTable
go