Object reference not set to an instance of an object – Stored Procedures

Object reference not set to an instance of an object – Stored Procedures

After making some changes to accounts and permissions on a webserver running an asp.net application I found that when trying to log in I received this error back from the webserver.

Object reference not set to an instance of an object.

The login process calls several stored procedures on the SQL Server (2005) that authenticate and grab user information. These stored procedures need to run under the Network Service account on the server which hosts both the webserver and the database.

After doing some quick searching, this error often implies, at least with stored procedures, that the procedure is not returning valid data to the object intended to store the results. I checked permissions on the SQL Server instance and found that Network Service did not ‘Execute’ permissions on the database so the stored procedures required for login were not running and therefore not returning data.

I gave execute permissions to the schema that Network Service is a member of on the required database and the application worked once more.

I’m curious as to what would have happened that removed these permissions from the account in the first place as the application was working fine before applying these OS and SQL Server updates and changing the account settings. I know something changed along the way but I’m not certain what.