Post reply

Order in which Stored Procedures are called

  • May 12, 2004 at 9:25 am

    #85658

    Sorry, I should know this...

    If a userid ABC1234 has EXEC access to all of the following SPs, and calls usp_get_data without qualifying the owner id, what is the order in which they will be selected?  (Feel free to point me toward the correct section of BOL - I'm apparently not using the correct terminology to search)

    dbo.usp_get_data

    ABC1234.usp_get_data

    DEF5678.usp_get_data

  • May 12, 2004 at 10:07 am

    #506171

    SQL will try and use the ABC1234 sp first.  IF the owner is not qualified SQL tries to 1st run the user.object then (I think) dbo. and then any other user.object...

    The system infers that NO owner equals current owner and NOT dbo. UNLESS the user is the dbo and then the system will try and find DBO 1st and then the userid and then any others.

    I think this is the way it works.  I was unable to find anything that pointed in this direction

    A good exercise would be to have each of the 3 have different SELECT statements and then you would be able to determine which ran 1st and try the different user is dbo, etc....

    Good Luck



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • May 12, 2004 at 12:16 pm

    #506188

    Thanks for the info - that is pretty much what I suspected, but couldn't find it in writing.

    If anyone else finds it in BOL, I'd still like to know where it is...   

     

  • May 13, 2004 at 1:50 am

    #506247

    Hello Dave,

    look at "Database Object Owner" - as far as I know, the same rules apply to all objects. Also, look at "Programming Stored Procedures" (qualifying names inside stored procedure) for some additional info about calling objects from inside a stored procedure.

    That's all I found in BOL... cheers,

    Vladan

  • May 13, 2004 at 4:27 am

    #506268

    Following is the excerpt from BOL -

    If an object is not qualified with the object owner when it is referenced (for example, my_table instead of owner.my_table), SQL Server looks for an object in the database in the following order:

    1. Owned by the current user.
    2. Owned by dbo.

    If the object is not found, an error is returned.

    This can be found in this path BOL-Contrents-Administering SQL Server - Managing Security - Creating Security Accounts - Database Object Owner

  • May 13, 2004 at 7:42 am

    #506311

    Thanks very much for the assistance! 

Viewing 6 posts - 1 through 5 (of 5 total)

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