从存储过程调用视图

发布于 2024-11-03 17:23:04 字数 502 浏览 2 评论 0原文

我有一个用户 VIEW，我们称之为 [MIKE].Table ，它执行过滤选择 SELECT * FROM TABLE WHERE TL_FILTERKEY in (1,2,3)

因此，如果 Mike 连接到服务器并执行，

"SELECT * FROM TABLE"

他将只能看到表中的部分数据。

还有一个具有 dbo 权限并允许 Mike 执行的过程，因为

CREATE PROCEDURE tbSelect
as
SELECT * FROM TABLE

如果 Mike 执行 exec tbSelect，他将看到表中的所有行，但不会被过滤。

如何编写该过程以便从用户视图（过滤后的行）中选择数据？

我尝试重新创建过程“WITH EXECUTE AS 'Mike'”和“WITH EXECUTE AS CALLER”，但是返回了所有行。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

最初的梦 2024-11-10 17:23:04

您的问题有点令人困惑，并且您没有提及您的 SQL Server 版本，但我怀疑您的问题是由于架构。当您引用没有架构的对象时，SQL Server 首先在用户的默认架构中查找对象，然后在 dbo 架构中查找。

作为一个完整的猜测，我认为您有一个名为 Mike 的用户，其默认架构也是 Mike、一个名为 Mike.Table 的视图和一个名为 dbo.Table 的表。当 Mike 运行他的查询时，Table 被解析为 Mike.Table，但是当 db_owner 查询 Table 时，它被解析为 dbo.Table.

为了避免混淆，您应该使用更清晰的对象名称（至少用于测试）并且始终使用所有者限定对象名称：

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

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

回复收藏 0 原文

~没有更多了~