Datasource creation: Login Failed: A Tip

This isn’t a question but I thought I’d drop this here in case anyone runs into this tearing your hair out issue.

In prep for sql2012 EOL in October, I stood up a new 2019 server instance, did a restore of database for testing. When trying to add a Lucee datasource using a sql server login, I kept getting a Login Failed error. I double & triple checked the server setup, firewall and that the user exists in the db logins…still the same error.

Turns out it’s an SQL server thing where “The SQL Server error 15023 User already exists in current database occurs when a databases is restored from another instance. The database users aren’t mapped to the corresponding logins at the instance where it is restored and are termed as orphaned users.”

The query at the bottom of this post loops through the orphans and fixes them up.

Might be a well duh post for many of you but it was new to me (not in a habit of restoring to new instances often) and since I’m sure there will be others preparing for 2012 EOL…maybe this will help someone. .