Execute as User vs Execute as Login

• krypto69

  SSChampion

  Points: 13606

  More actions

  January 16, 2019 at 1:16 pm

  #382594

  Hi 

  Having trouble figuring out why:

  This fails:

  EXECUTE AS USER      = 'premier\reports' 

  SELECT * FROM pecMaster.dbo.CountyToFloridaMMARegion 

  REVERT

   
  This works (probably because its server login level – as opposed to DB user level) 

  EXECUTE AS LOGIN     = 'premier\reports' 

  SELECT * FROM pecMaster.dbo.CountyToFloridaMMARegion 

  REVERT

  Any ideas as to why this is happening?

• crow1969

  Hall of Fame

  Points: 3001

  More actions

  January 16, 2019 at 1:28 pm

  #2019140

  Ultimately, it depends on the error message.  My guess would be that the stored procedure is accessing more than just the local database, especially if you are calling it by the three-part-name.  Execute as User limits your scope to the local database.  Everything outside the local database is inaccessible.