Post reply

Cross Database Permissions: SQL 2005 vs SQL 2016

  • 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 🙂

  • June 27, 2018 at 6:41 am

    #1995429

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

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply