Ever heard of orphaned users? It’s a database user account, disconnected from its associated login. You typically get this when you take a database backup, and restore it to a different server.
Microsoft provides us with the ever helpful sp_change_users_login to find and repair orphaned users, but even so, it’s kind of a PITA* manual operation. To simplify and provide for automation, I’ve put together the brand-spanking-new sp_FixOrphanedUsers.
First, let’s orphan some users
If you’d like to test this, here is a repro scenario that creates an orphaned user on a single instance. It orphans a user by doing the following:
- Create a login “OliverTwist”**.
- Create a database.
- Create a user in that database from the “OliverTwist” login.
- Back up the database, then drop it.
- Drop the login.
- Restore the database.
I will say that steps 4 and 6 aren’t strictly necessary; just dropping the login will orphan the user. But I’d like to drill the idea of orphans being associated with restores, because that’s the most common scenario in real life.
Important: ALWAYS review code before you run it, even on a test instance.
Now let’s resolve a single orphaned user
When you have an orphaned user, it’s really easy to detect and fix them. In our case, we’d find the orphan by running
EXEC OrphanedUserDemo..sp_change_users_login 'Report';
And then, we could fix that orphan by first creating the missing login, and then running
EXEC OrphanedUserDemo..sp_change_users_login 'AUTO_FIX', 'OliverTwist';
(Or, if we want the missing login to be created automatically, then “EXEC OrphanedUserDemo..sp_change_users_login ‘AUTO_FIX’, ‘OliverTwist’, NULL, ‘NewPasswordOMG!!!’;“)
But this is a manual process, and therefore slow and tedious and not entirely worth our time.
Let’s resolve many orphaned users
Create the stored procedure sp_FixOrphanedUsers in master, and run it as [DBName]..sp_FixOrphanedUsers, and it fixes all orphaned users in that database, if it can do so automatically.
Better still, you could run sp_FixOrphanedUsersAllDB (included) to run this process against all databases on the SQL Server instance.
If you have a regular refresh process – like restoring a set of databases to UAT – you could create a second step in the job to run sp_FixOrphanedUsersAllDB, and greatly simplify your life.
Limitations and caveats
Of COURSE there are limitations, silly reader!
- Don’t trust code you download from the web. ALWAYS review code before you run it, even on a test instance.
- This is the first iteration of the procedure(s). Doubtless, you and other readers will have helpful suggestions on how to improve the thing.
- In this iteration, the SP doesn’t have the ability to specify a password to auto-create missing logins. I think this is a really bad practice. (“Oh, this server doesn’t have a login for ‘GodlyAdminGuy’? Let’s create on with ‘P@ssw0rd!’ for the default!” Yeah, no.)
- And other things I haven’t yet thought of, but will doubtless be pointed out to me enthusiastically and repeatedly. Feel free, but be cool.
*PITA = “Pain In The Analytics”, so far as you know.
** GET IT??