Failed SQL Login - Source server same as destination cannot locate SPID SQL Server 2014
I've seen this before.  I believe that it was due to autoclose.  But I could not recall from memory.  Credit here goes to Aaron Bertrand.


  Usually this means the login's default database is offline (perhaps due to AutoClose) or no longer exists.  Resolve by fixing the missing database, or changing the login's default database using ALTER LOGIN (for older versions, use sp_defaultdb, which is now deprecated).  This is reported as state 16 prior to SQL 
  
  Server 2008. Error: 18456, Severity: 14, State: 40. Login failed for
  user ''.  Reason: Failed to open the explicitly specified database.


Blog post regarding SQL login errors