The cause was traced to incorrect database permissions, i.e.
Server: Msg 229, Level 14, State 5, Procedure xyz, Line 123
SELECT permission denied on object 'abc', database 'pqr', owner 'dbo'.
The question was, why did this not cause an exception? A possible cause is our use of SqlDataReader, but I was unhappy with the suggested fix. The underlying cause however was that nocount was set to off (its default setting). This caused the row count messages to mask the error, and thus no exception was raised.
Lesson learned: always set nocount on at the start and off at the end of each stored procedure, e.g.
create procedure xyz
as
begin
set nocount on
-- SP SQL
set nocount off
end
go
