0

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!

1

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.

  • That's got it! I had checked to ensure (almost) everything else including the DB owners, but had TOTALLY omitted the chaining thing. Awesome, thank you, and I'm glad it was just me being an idiot rather than anything else lol! :D – ContractDeebz Jun 27 '18 at 11:59
  • Item 3 did it for me. Turns out Database A for me had owner sa and Database B had another SQL login. – fujiiface Dec 28 '18 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.