Thanks Victor, I appreciate the assistance.
I returned to this problem this morning and had an ah-ha moment. In migrating my newly-upgraded 4.1 website to the destination server I copied the application files to the server and then performed a restore of the database to that server, restoring on top of the existing pre-4.0 database. What I failed to do was re-link the login, as during the restore since the SQL login already exists on that server the database's restore login becomes orphaned.
To fix this, I ran the following SQL query against my Sitefinity 4.1 database (where 'app_sitefinity4' is the login defined in my Data.config):
Which gave me the expected output (note the # of orphaned users that was fixed).
The row for user 'app_sitefinity4' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.
After recycling the application pool for the website everything began to run perfectly.
Perhaps this is a bit of unexpected functionality for the baked-in 4.1 upgrade? (That is, the internal upgrade process when deploying the 4.1 application files that will implicitly attempt to upgrade any pre-4.1 database to the proper version)
Steps to reproduce:
1. On the destination server have the un-upgraded Sitefinity 4.0 site already running.
2. From the development server/workstation take a backup of the upgraded Sitefinity 4.1 database.
3. From the development server/workstation copy the Sitefinity 4.1 application files to the destination server, overwriting the existing files when prompted.
4. On the destination server restore the Sitefinity 4.1 database (from step #2) over-top the existing 4.0 database.
5. Launch a browser and hit the 4.1 site -- Will receive error Database '----' already exists
Run sp_change_users_login with the Auto_Fix option, targeting the SQL login defined in the Data.config.