June 27, 2018 at 5:44 am
Hi there,
Question:
Both servers are running SQL Standard - (though one is 2005 and one is 2016).
To summarize:
SQL2005
SQL2016
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 🙂
June 27, 2018 at 6:41 am
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.
June 27, 2018 at 9:52 am
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
June 27, 2018 at 12:08 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply