synonyms permission

• jagat.patel

  SSC Eights!

  Points: 908

  More actions

  February 6, 2009 at 3:13 pm

  #128661

  I have two database having same table object say tbl

  db1 and db2

  Now I created a schme sch in db1

  synonyms in db1 for db2.tbl in that schma

  so now db 1 has dbo.tbl and sch.tbl

  and I created procedure proc

  as

  select * from dbo.tbl

  UNION ALL

  select * from sch.tbl

  and grant execute permission to user test

  this user dose not have select permission on any of das

  when i run this proc it gives error on db2.tbl that

  The SELECT permission was denied on the object

  what is the bast way without givint user a select permission I can run this proc

  Thanks

• TRACEY-320982

  SSChampion

  Points: 13653

  More actions

  February 6, 2009 at 3:19 pm

  #939837

  You have to right click on db2.tbl and give the user rights to read this table.

  Perhaps use a ROLE and just give select rights to the user.

  Put user in a ROLE and then just give db2.tbl select rights only.