SharePoint 2013: Cannot open database requested by the login

Problem

You have changed the database of a service application.  You did this by changing the database name in the service application's Properties page (in Central Administration).  After changing the database, the service application is restored without any issues and runs successfully.  You are able to interact successfully with the service application.  Some time later, during routine checks, you find the following error event messages appearing in the server application log of one of your farm's Application servers:

Critical Event 3760
SQL Database '[Service Application Database]' on SQL Server instance  '...' not found. Additional error information from SQL Server is included below.
Cannot open database "[Service Application Database]" requested by the login. The login failed.
Login failed for user 'DOMAIN\[farm service account]'.

Similar messages are seen in the ULS log.  Curiously, the user associated with these events is your own administrator account.  Checking the Manage Databases Upgrade Status page and find the old service application database still listed. Launching SQL Server Management Studio and then connecting to the farm's database server instance, you verify that the old database is still attached.

The cause of the problem is that the old database has not been formally removed from the farm.

Solution

1. First, review all farm databases and their status.  You can do this by executing this command in an elevated SharePoint shell:

   Get-SPDatabase | Sort-Object Name | ft Name, ID, IsAttachedToFarm, ExistsInFarm,Exists,Status -auto

   The Exists status of the orphaned database will be False.
2. Note down the ID of the database having Exists status of False.
3. Next, get an instance of the orphaned database by executing this command:

   $orphanDB = Get-SPDatabase -Identity [ID]

4. Verify that an instance was returned by simply executing:

   $orphanDB

5. Now remove the database from farm configuration by using this command:

   $orphanDB.Delete()

   This removes the database from the farm, but not from SQL Server nor does it close any open connections. To clean up, you have one more command to run
6. Execute this command to clean up the farm and detach the database from SQL Server:

   $orphanDB.Unprovision()

7. Lastly, rerun this command to verify that the farm no longer retains any reference to the orphaned database:

   Get-SPDatabase | Sort-Object Name | ft Name, ID, IsAttachedToFarm, ExistsInFarm,Exists,Status -auto

   And then check the Manage Databases Upgrade Status page again.
   

References

• Get-SPDatabase
• Cleaning up deleted databases in SharePoint
• Deleting Orphaned SharePoint Databases
• SecureStoreServiceDatabase.Unprovision method
• Manage databases in SharePoint 2013
• Rename service application databases in SharePoint 2013

Notes

• This farm has one application server.
• Central Administration > Upgrade and Migration > Review database status > Manage Databases Upgrade Status
• I encountered this problem, originally, by restoring to the development farm the managed metadata database of the production farm.