Inherited Permissions: SQL Server 2005 vs 2016 Question:


I have a SQL Server 2005 server and a SQL Server 2016 server.
On each server is a database (A) with a stored procedure that selects data from a view in Database (A)
The view in database (A) is referencing a table in Database (B) on THE SAME SERVER
I am using a logon that has ONLY got execute permission on the stored procedure on database (A).
This setup is the same for BOTH servers.
On the 2005 server, I can run the SP as the described logon and retrieve data via the view in database (A) from the table in database (B) - as I would expect.
On the 2016 server, I get an error because from the database (A) I cannot select, via the SP and the view, data from the table in database (B).


Both servers are running SQL Server Standard Edition - (though one is 2005 and one is 2016).

To summarize:

SQL Server 2005 


DATABASE A = VIEW = TABLE in DATABASE B
DATABASE A = STORED PROCEDURE = select from VIEW
LOGON = ONLY EXECUTE ON STORED PROCEDURE
RESULT = SUCCESS


SQL Server 2016


SET UP APPEARS IDENTICAL
RESULT = FAIL


Why is this?
What is different between these two versions of SQL with regards to inheritance of permissions?
Is it something very obvious that I am missing?

Any pointers would be very helpful!
I would not expect the different SQL Server versions to make a difference here. Although the setup might appear identical, there are likely subtle differences between the databases on the 2 servers or the server config. 

The first thing I suggest is to verify requirements cross-database ownership chaining are met:


Ensure the 'cross db ownership chaining' configuration option is the same on both servers
Make sure the DB_CHAINING database option is identical
Check that the database owners are the same to maintain an unbroken ownership chain for dbo-owned objects


If the culprit is the 'cross db ownership chaining' server option, it would be better leave that off and instead turn on the more granular DB_CHAINING database option for the SQL 2016 databases in question.

Post the actual error message if you still have problems after the above.