The SQL code was failing for one DB User with DBO permissions on the databases it was accessing. 

Problem

There was one stored procedure under database DB1 at some point it used to call other stored procedures from DB2. This caused failure when tried to analyse the issue using DEBUG method.

SOLUTION: 

Its fixed for us by doing following changes at code and DB level.

Code Level: 

11 )Impersonate method

With execute as [login]

Execute as login=[login name]

22) Fully qualified database object names

Administration Side:

11) Grant user granted public role on master to read objects.

22)Granting View Server State at instance level.