MS SQL 2014 cross-database access does not work as I expect

Question

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?

Answer 1

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!