SQL errors don't cause exceptions if nocount is off

We had a strange problem today: an end-user was getting unexpected results (no data), but no exceptions were occurring.

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