SQL Server: Error – Web application can’t import data from a file to SQL server anymore

Related information:

• This happens afer database migration is done from SQL 2008 to SQL 2015

Solution

• Reason:
  The workaround was to ensure that the Web DB account had public permission for the Master and MSDB databases, and also, and more relevantly to this ticket, the recovery model for the database had to be ‘Bulk Logged’ not Simple or Full.
  During testing, we found that if the recovery model was Simple or Full, the system was calling a stored procedure named sp_dboption.
  I suspect it was calling this SP to change the database to bulk logging and then possibly changing it back again. I’m not sure why it would do this tho’.
  This SP is deprecated and does not even exist in SQL2015 and was thus causing an error. This SP was also deprecated in SQL2012, but it didn’t cause an error that stopped it working until SQL2015.
  We need to rewrite the code (possibly in a SP) so that it doesn’t try to use the deprecated SP and so that a database can have a recovery model of Full or Simple without the file upload failing.
  The following link may give some insight into sp_dboption alternative, and some of the comments regarding truncating logs at chkpt and changing recovery model may also be relevant to why our file upload is using it.
  https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fc18f418-7285-4c1a-89af-9f20b023ac2c/alternate-option-for-spdboption-in-sqlserver-2012?forum=sqldatabaseengine
• Action:
  Required the database user account which is allowed to access from Web user (IIS user) to have Master and MSDB added to mapping (public is enough, no need for admin or anything) and the recovery model had to be Bulk Logged, not Simple or Full.