MS SQL 2014跨数据库访问不能像我期望的那样工作(MS SQL 2014 cross

MS SQL 2014跨数据库访问不能像我期望的那样工作(MS SQL 2014 cross-database access does not work as I expect)

我有两个数据库。 一个有数据，一个有存储过程。 用户不应该直接读取数据，而只能通过存储过程读取数据。 在服务器和所有数据库上启用标志“跨数据库所有权链接”。

作为SA，我正在做：

use data_db create table dbo.t(....) insert into t values(....) use sp_db create procedure dbo.readt as select * from data_db.dbo.t grant execute on dbo.readt to user1

现在我重新连接为user1

execute sp_db.dbo.readt

我收到一个错误：

服务器主体“user1”无法访问当前安全上下文下的数据库“data_db”。

我究竟做错了什么？

I have two databases. One with data and one with stored procedures. A user should not be able to read data directly, but only through stored procedures. Flags "Cross-Database Ownership Chaining" are enabled on the server and on the all databases.

As SA I am doing:

use data_db create table dbo.t(....) insert into t values(....) use sp_db create procedure dbo.readt as select * from data_db.dbo.t grant execute on dbo.readt to user1

Now I reconnect as user1

execute sp_db.dbo.readt

and I am getting an error:

The server principal "user1" is not able to access the database "data_db" under the current security context.

What am I doing wrong?

最满意答案

你需要execute as ：

create procedure dbo.readt with execute as owner as begin select * from data_db.dbo.t; end;

然后，存储过程具有创建它的人的权限，而不是用户调用它的权限。

The problem is solved.

My initial desire was that regular users were not be able to do 'use data_db' at all, since they are not allowed to read any data from data_db directly. Apparently the server cannot work like that... Strange but ok.

as SA:

use data_db grant connect to user1

The user is now able to do 'use data_db' command, but nothing else. The CDOC finally works!