problem with Login from DB1 to DB2

• tony28

  SSCertifiable

  Points: 7108

  More actions

  September 18, 2014 at 1:42 am

  #299451

  Hello,

  I tried few solution but still I am not satisfied

  example

  Login1 has access just to DB1

  Login2 has access to all DBs

  I created with Login2 procedure in DB1 where is table from DB2

  I tried executed with Login1 and there was error

  The SELECT permission was denied on the object 'TB_TABLE', database 'DB2', schema 'dbo'.

  So I tried change procedure to execute as Owner or directly Login2

  And execute procedure with Login1

  there was next error

  The server principal "Login2" is not able to access the database "DB2" under the current security context.

  Is it possible does something? I dont understand how is possible when I am connected with Login2 in DB2 and normally I have access and is not possible to give access to all this tables what I have in this procedure.

  So my solution is this, but I dont want to have like this.

  Solution:

  I created next procedure2 in DB2

  Modify procedure1 in DB1 where now is just exec procedure2 from DB2

  Login1 has access to execute procedure2 from DB2

  with Login1 I just execute procedure1 in DB1 , there was next error

  The SELECT permission was denied on the object 'TB_TABLE', database 'DB2', schema 'dbo'.

  so I added to procedure2 in DB2 WITH EXECUTE AS OWNER or Login2

  Login1 again execute procedure1 in DB1 and now I have Result