How to Identify and Fix Orphaned Users in SQL Server (And Why It Matters)

Orphaned users in SQL Server are like ghost accounts – they exist in your database but have no valid login at the server level. This misalignment often happens after database restores, migrations, or disaster recovery operations. If left unchecked, orphaned users can cause failed logins, broken apps, and security risks.

In this post, we’ll show you exactly how to identify and fix orphaned users in SQL Server, why it matters, and how to keep your environment clean and secure.

What Are Orphaned Users?

In SQL Server, a user is tied to a server-level login via a Security Identifier (SID). When you move a database to another server or restore from backup, the SID mapping can break – leaving users without a corresponding login. These are your orphaned users.

Why should you care?

• Operational Headaches: Troubleshooting access issues takes time and slows down teams.
• Security Risks: Ghost accounts can be hijacked or misused.
• Broken Applications: Apps relying on specific database users might start throwing authentication errors.

How to Identify Orphaned Users in SQL Server

Here’s a simple, battle-tested T-SQL script to help you spot orphaned users:

USE YourDatabaseName;
GO

EXEC sp_change_users_login 'Report';

Alternatively, in more modern versions (SQL Server 2012+), you can use:

SELECT dp.name AS OrphanedUser
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type_desc = 'SQL_USER'
	AND sp.sid IS NULL;

Pro Tip: Always run this after a database restore to a new server.

How to Fix Orphaned Users

There are two ways to fix this, depending on whether the login exists:

1. Re-map to an existing login

USE YourDatabaseName;
GO

EXEC sp_change_users_login 'Auto_Fix'
	,'YourUserName'
	,NULL
	,'StrongPassword';

Heads up: sp_change_users_login is deprecated, but still handy in SQL Server 2019 and earlier.

2. Create a missing login and re-map
   If the login is missing, create it first:

CREATE LOGIN YourUserName
	WITH PASSWORD = 'StrongPassword';
GO

ALTER USER YourUserName
	WITH LOGIN = YourUserName;

Real-World Case

We were brought in by a mid-size financial services company that had just spun up a refreshed test environment, full restore from production. Seemed routine, until nothing worked. Their main application couldn’t connect to the restored database, despite all the credentials being “the same.”

Error logs showed failed logins for users that definitely existed in the database, but not on the server.
Classic orphaned users.

We logged in, ran:

EXEC sp_change_users_login 'Report';

– and sure enough, a dozen orphaned accounts showed up, including their main service account.

But here’s the kicker: their original production server had logins tied to a domain controller that wasn’t accessible from the test subnet. Even if they’d recreated the logins, the SIDs wouldn’t have matched.

So we did what any DBA worth their salt does: manually recreated the logins with the right SIDs, using CREATE LOGIN ... SID = 0xABCDEF..., and re-mapped the users with ALTER USER.

App access was restored in under 20 minutes. If they hadn’t flagged it early, their QA team would’ve wasted half the day troubleshooting “permissions” issues that had nothing to do with permissions.

Final Thoughts

Leaving SQL Server orphaned users lying around is like leaving your server doors unlocked. It’s a small issue – until it breaks something or exposes you to risk. Clean it up today.

At Red9, we help teams clean up their SQL Server environments, fast. If you’re tired of patching the same issues over and over, let’s talk.