- 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).
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!