SQL Server – Inherited Permissions Differences Between 2005 and 2016

permissionssql-server-2005sql-server-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!

Best Answer

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.

Related Question