Cross Database Permissions: SQL 2005 vs SQL 2016

• ambientphon

  Newbie

  Points: 8

  More actions

  June 27, 2018 at 5:44 am

  #352282

  Hi there,

  Question:

  • I have a SQL 2005 server and a SQL 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 Standard - (though one is 2005 and one is 2016).

  To summarize:

  SQL2005

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

  SQL2016

  • SET UP APPEARS IDENTICAL
  • RESULT = FAIL

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

  Any pointers would be very helpful!

  Thanx 🙂

• Phil Parkin

  SSC Guru

  Points: 246695

  More actions

  June 27, 2018 at 6:41 am

  #1995429

  Rather than 'FAIL', please post the exact text of the message you see.

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• ScottPletcher

  SSC Guru

  Points: 100948

  More actions

  June 27, 2018 at 9:52 am

  #1995477

  Check to see if the db A on each instance allows cross-database ownership chaining:

  SELECT is_db_chaining_on, *
  FROM sys.databases
  WHERE name IN ('A')

  If it's 1, then security would not be checked when going from db A to db B.
  If it's 0, it would be checked.
  Hopefully that can explain what is happening.

  But don't just instantly turn chaining back on, because that opens up a lot of serious security issues.  You'd be better off just GRANTing the user limited permissions on db B.

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• Russel Loski

  SSCertifiable

  Points: 7056

  More actions

  June 27, 2018 at 12:08 pm

  #1995497

  My recollection is that cross database ownership chaining was on by default in earlier versions of SQL Server and was off by default in later versions of SQL Server.  I don't recall when this change occurred. It may have been before 2005.  Also, there are system configurations you can set to allow cross database ownership chaining, which may result in different behaviors.

  Russel Loski, MCSE Business Intelligence, Data Platform