locked
Unexpected behavior stored procedure permissions RRS feed

  • Question

  • Hi all,

    I have a question on stored procedure permissions that puzzles me...

    Situation A
    In database A I have created a SP that does a simple select * from a table in the same database A. I have made a SQL login and a user for this login in database A. I have given the user execute rights on the SP, nothing more. So I have not given the user select rights on the table the SP uses and no datareader rights on the database. After logging in as the testuser I can successfully execute the SP.

    Situation B
    This is roughly the same besides the fact that the SP now does a select * from a table in a different database (B). Again I have made a SQL login and user and gave it execute rights on the SP. I somehow expected the SP to execute successfully but it did not. Only after making a user for the login in database B too as well as giving this user read rights on the table the SP executes successfully.

    The idea I had behind using a SP to get the data from database B was that I would not have to give the user rights for the database/table itself an could just have all communications go safely through the SP.

    Thinking error...
    Of course I am making a thinking error here... My expectation was that, as in both cases I only grant the user execute rights for the SP and no rights for the database in which the used table resides or for the table itself, it would act the same.

    But it obviously does not, it seems to depend on in which database the SP itself is present. Where does my thinking go wrong?

    Thanks,
    Erik



    • Edited by Erik van Dongen Wednesday, March 20, 2013 4:35 PM
    • Moved by Tom Phillips Wednesday, March 20, 2013 6:57 PM Security question
    Wednesday, March 20, 2013 4:34 PM

Answers

  • Erik,

    Situation A
    Could you write a repro script for A? I'm assuming that ownership chaining is at work here. Who created/owns the table?

    Situation B
    This should fail for a few reasons. If ownership chaining would be used, DB_CHAINING needs to be turned on along with TRUSTWORTHY. If those are not on, ownership chaining will fail.

    A few things come to mind: Signed stored procedures, Secure dynamic SQL execution, extremely low security accounts.

    -Sean


    Sean Gallardy | Blog | Twitter

    • Proposed as answer by Fanny Liu Thursday, March 21, 2013 3:20 AM
    • Marked as answer by Erik van Dongen Thursday, March 21, 2013 10:10 AM
    Wednesday, March 20, 2013 4:45 PM
  • This is normal and expected.

    Situation A:

    If the table and the stored proc use the same schema, it does not check rights to objects used by the stored proc.

    Situation B:

    requires "cross-database ownership chaining"

    Please see: http://msdn.microsoft.com/en-us/library/ms188676(v=sql.105).aspx

    • Proposed as answer by Fanny Liu Thursday, March 21, 2013 3:20 AM
    • Marked as answer by Erik van Dongen Thursday, March 21, 2013 10:11 AM
    Wednesday, March 20, 2013 6:57 PM
  • Situation B
    This should fail for a few reasons. If ownership chaining would be used, DB_CHAINING needs to be turned on along with TRUSTWORTHY. If those are not on, ownership chaining will fail.

    TRUSTWORTHY does not need to be on, but the databases need to have the same owner.

    For a much longer discussion on the topic, see this article on my web site:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Fanny Liu Thursday, March 21, 2013 3:21 AM
    • Marked as answer by Erik van Dongen Thursday, March 21, 2013 10:11 AM
    Wednesday, March 20, 2013 10:34 PM

All replies

  • Erik,

    Situation A
    Could you write a repro script for A? I'm assuming that ownership chaining is at work here. Who created/owns the table?

    Situation B
    This should fail for a few reasons. If ownership chaining would be used, DB_CHAINING needs to be turned on along with TRUSTWORTHY. If those are not on, ownership chaining will fail.

    A few things come to mind: Signed stored procedures, Secure dynamic SQL execution, extremely low security accounts.

    -Sean


    Sean Gallardy | Blog | Twitter

    • Proposed as answer by Fanny Liu Thursday, March 21, 2013 3:20 AM
    • Marked as answer by Erik van Dongen Thursday, March 21, 2013 10:10 AM
    Wednesday, March 20, 2013 4:45 PM
  • This is normal and expected.

    Situation A:

    If the table and the stored proc use the same schema, it does not check rights to objects used by the stored proc.

    Situation B:

    requires "cross-database ownership chaining"

    Please see: http://msdn.microsoft.com/en-us/library/ms188676(v=sql.105).aspx

    • Proposed as answer by Fanny Liu Thursday, March 21, 2013 3:20 AM
    • Marked as answer by Erik van Dongen Thursday, March 21, 2013 10:11 AM
    Wednesday, March 20, 2013 6:57 PM
  • Situation B
    This should fail for a few reasons. If ownership chaining would be used, DB_CHAINING needs to be turned on along with TRUSTWORTHY. If those are not on, ownership chaining will fail.

    TRUSTWORTHY does not need to be on, but the databases need to have the same owner.

    For a much longer discussion on the topic, see this article on my web site:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Fanny Liu Thursday, March 21, 2013 3:21 AM
    • Marked as answer by Erik van Dongen Thursday, March 21, 2013 10:11 AM
    Wednesday, March 20, 2013 10:34 PM
  • Thanks guys! I wil definitely look into ownership chaining tomorrow and report back.

    Have been working with SQL for about 10 years now and so far never came accross this...

    Still learning new things every day!

    EDIT
    Had a good look at all the links you guys provided and have succesfully set up cross database ownership chaining for the stored procedures and functions in question. Thanks again!

    Cheers,
    Erik

    • Edited by Erik van Dongen Thursday, March 21, 2013 10:10 AM Testen and solved my problem
    Wednesday, March 20, 2013 10:40 PM