When administering Microsoft SQL Server, especially during backup restores, server migrations, or development refreshes, a common issue that surfaces is the orphaned database user. In this blog post, we’ll explore all possible ways to detect and fix orphaned users in SQL Server — using T-SQL scripts, system views, and modern alternatives to deprecated stored procedures.
What Are Orphaned Users?
An orphaned user is a database-level user that is no longer associated with a corresponding server-level login. This usually happens when:
- A database is restored on a different server
- A login is deleted or not recreated during migration
- A login is created with a different SID (Security Identifier)
These users can exist in the database, but won’t have permission to connect or perform actions — causing “Login failed” or “User not associated with a trusted SQL Server connection” errors.
Method 1: Detect Orphaned Users Using sp_change_users_login
USE [YourDatabaseName];
GO
EXEC sp_change_users_login 'Report';This lists all SQL users in the database that don’t have matching logins at the instance level.
⚠️ Note: This only works for SQL Server Authentication users (type = 'S'), and is deprecated in SQL Server 2012+.
Method 2: Detect Using System Views (Recommended)
SELECT dp.name AS OrphanedUser
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U', 'G') -- SQL user, Windows user/group
AND dp.authentication_type = 1 -- SQL Authentication
AND sp.sid IS NULL
AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys');This works for SQL and Windows logins and doesn’t rely on deprecated features.
Method 3: Fix with sp_change_users_login (Auto Fix)
USE [YourDatabaseName];
GO
EXEC sp_change_users_login 'Auto_Fix', 'username';It maps the orphaned user to an existing login with the same name and password.
Method 4: Manual Mapping with sp_change_users_login 'Update_One'
USE [YourDatabaseName];
GO
EXEC sp_change_users_login 'Update_One', 'UserName', 'LoginName';✅ Use this if the login exists but the name is different.
Method 5: Create Missing Login, Then Map It
If the login doesn’t exist, create it manually and map it:
-- Create a new login
CREATE LOGIN [OrphanedUserName] WITH PASSWORD = 'StrongPassword!';
GO
-- Map it to the existing user
USE [YourDatabaseName];
GO
EXEC sp_change_users_login 'Update_One', 'OrphanedUserName', 'OrphanedUserName';Method 6: Use ALTER USER (Modern & Recommended)
Since sp_change_users_login is deprecated, Microsoft recommends using ALTER USER:
USE [YourDatabaseName];
GO
ALTER USER [UserName] WITH LOGIN = [LoginName];✅ Works for both SQL and Windows users. It’s the future-proof way to fix orphaned users.
Method 7: Recreate Both Login and User (Clean Slate)
In some cases, you may want to drop and recreate both the login and user:
-- Drop user from DB (if required)
USE [YourDatabaseName];
GO
DROP USER [UserName];
GO
-- Drop login from server
DROP LOGIN [LoginName];
GO
-- Recreate login
CREATE LOGIN [LoginName] WITH PASSWORD = 'YourStrongPassword';
GO
-- Add user to DB
USE [YourDatabaseName];
GO
CREATE USER [UserName] FOR LOGIN [LoginName];You’ll need to reassign roles and permissions after recreating.
BONUS: Script to Fix All Orphaned Users Automatically
USE [YourDatabaseName];
GO
DECLARE @username NVARCHAR(100);
DECLARE orphaned_users CURSOR FOR
SELECT dp.name
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type = 'S'
AND dp.authentication_type = 1
AND sp.sid IS NULL
AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys');
OPEN orphaned_users;
FETCH NEXT FROM orphaned_users INTO @username;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Fixing orphaned user: ' + @username;
BEGIN TRY
EXEC sp_change_users_login 'Auto_Fix', @username;
END TRY
BEGIN CATCH
PRINT 'Error fixing user: ' + @username + ' - ' + ERROR_MESSAGE();
END CATCH;
FETCH NEXT FROM orphaned_users INTO @username;
END
CLOSE orphaned_users;
DEALLOCATE orphaned_users;Why Fixing Orphaned Users Is Important
- ❌ Avoids login failures and permission errors
- ✅ Ensures proper user-role access mapping
- 🔐 Maintains security compliance
- 🔄 Essential during DR, backup restores, and migrations
Summary Table
| Task | T-SQL Command |
|---|---|
| Report orphaned users | EXEC sp_change_users_login 'Report' |
| Detect using system views | Use sys.database_principals + LEFT JOIN |
| Auto-fix matching users | EXEC sp_change_users_login 'Auto_Fix', 'username' |
| Manual fix | EXEC sp_change_users_login 'Update_One', 'user', 'login' |
| Modern fix | ALTER USER [username] WITH LOGIN = [loginname] |
| Recreate user/login | DROP, CREATE LOGIN, CREATE USER |
| Bulk fix script | Use cursor-based script above |
Final Thoughts
As a SQL Server administrator, fixing orphaned users should be a part of your post-restore checklist. Whether you’re refreshing a dev environment or executing a disaster recovery drill, ensuring users are properly mapped saves time and avoids downtime.
💬 Have a method of your own? Share it in the comments!
📥 Stay tuned for more SQL Server best practices and automation tips — only on OurTechIdeas.com.
