Query on Two Database with Different Users

• James Tech

  Right there with Babe

  Points: 762

  More actions

  April 13, 2009 at 12:54 pm

  #203580

  I have two databases, lets just name it as Database_A and Database_B. Database_A has a security user "myuser" and Database_B has lets say security user "second_user". How do I deal with a query accessing certain tables in these two databases with different user grant?

  I have a stored procedure that resides on Database_A which I grant permission on "myuser":

  GRANT EXECUTE ON [dbo].[My_StoredProcedure] TO [myuser].

  but "myuser" does not exist on Database_B which I utilize to query on a certain table inside My_StoredProcedure. I mean what is the best solution to this kind of scenario? Do I need to grant each user to a certain database manually? or Do I need to detect a user on the other database and dynamically GRANT it?

  Please advice.

  Thank you for your time.

• prem

  Mr or Mrs. 500

  Points: 508

  More actions

  April 13, 2009 at 4:27 pm

  #976953

  Hi,

  You can change the execution context of Stored Procedure using EXECUTE AS clause while creating Stored Procedures.

  Different options available are { CALLER | SELF | OWNER | 'user_name' }

  Please explore these options for your requirement.

  Thanks

  Prem