Call view from Stored Procedure

Question

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.

Answer 1

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