Fix Orphaned Users, Instance-Wide

The Overview

Orphaned users are database principals that are no longer associated with a server principal, or login. This happens frequently when restoring a database from one server onto another server that doesn’t have the same pre-existing logins as the original server. This post shows how to list instance-wide orphaned users on a per-database basis. FYI, database principals, or users, and server principals, or logins, are linked not by name, but by security identifier, or sid. So, it’s not enough to create logins on the new server with the same name, they need to be created using the WITH SID = ... syntax in the CREATE LOGIN statement, where the SID value is the same value as the login on the source server. This post shows you how to fix orphaned users, instance wide, for every database including system databases.

fix orphaned users

     Maybe these guys are orphans, maybe they aren’t.

Identify the Little Buggers.

The code below shows orphaned users for every database in the instance, including system databases. It uses dynamic-SQL to fill the @cmd variable with a query for each online, non-contained database that looks for database users with no corresponding server login.

Microsoft Docs has a page dedicated to detecting and resolving orphaned users. However, when you have a large number of databases with this problem, the code above is helpful at identifying orphaned users across all databases at once.

Ok, Let’s Fix ‘Em!

The next piece of code helps fix orphaned users by reconnecting them to logins that have precisely the same name, but a differing SID. This code is a variant of the above code that dynamically creates ALTER USER statements. A statement is created for each orphaned user where there is a match-by-name in the list of server logins. Once the list of dynamically created ALTER USER statements are compiled, the commands to fix orphaned users are automatically executed.

Let me know if you found this script useful, or if you find some edge-case that I haven’t covered.

This post is part of our series on security