Fixing Orphaned Logins in SQL Server After Database Restore

 # Fixing Orphaned Logins in SQL Server After Database Restore

When restoring a SQL Server database from a backup, you may encounter orphaned users—database users that are no longer mapped to a valid server login. This issue occurs because SIDs (Security Identifiers) of logins do not always match between different SQL Server instances.

In this guide, we will cover:

• How to identify orphaned users

• How to fix orphaned users

• How to check user SIDs

• Simulating orphaned users for testing

---

1. Identifying Orphaned Users

To check for orphaned users in your database, use the following command:

USE [YourDatabase];
EXEC sp_change_users_login 'Report';

This will return a list of orphaned users in the database.

---

2. Fixing Orphaned Users

A. If the Login Exists on the Server

If the corresponding login exists on the server but is not mapped correctly, you can fix it using:

USE [YourDatabase];
EXEC sp_change_users_login 'Auto_Fix', 'YourOrphanedUser';

B. If the Login Does Not Exist

If the login was deleted or does not exist on the SQL Server instance, recreate it and map it back:

CREATE LOGIN YourLoginName WITH PASSWORD = 'YourStrongPassword';
USE [YourDatabase];
EXEC sp_change_users_login 'Update_One', 'YourOrphanedUser', 'YourLoginName';

C. Alternative Using ALTER USER (For SQL Server 2012+)

Instead of sp_change_users_login, you can use:

USE [YourDatabase];
ALTER USER YourOrphanedUser WITH LOGIN = YourLoginName;

---

3. Checking User SIDs

To understand why orphaned users occur, check the SID (Security Identifier) associated with a login or user.

A. Check SID of a Server Login

SELECT name, sid FROM sys.server_principals WHERE name = 'YourLoginName';

B. Check SID of a Database User

USE YourDatabase;
SELECT name, sid FROM sys.database_principals WHERE name = 'YourUserName';

C. Match Database User to Server Login (Find Orphaned Users)

SELECT dp.name AS DatabaseUser, dp.sid AS DatabaseSID, sp.name AS ServerLogin, sp.sid AS ServerSID
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U', 'G');

If ServerLogin is NULL, the database user is orphaned.

---

4. Simulating Orphaned Users for Testing

If you want to demonstrate orphaned users, you can manually create one by:

Step 1: Create a Login and Database User

CREATE LOGIN TestLogin WITH PASSWORD = 'StrongPassword!';
USE AdventureWorks;
CREATE USER TestUser FOR LOGIN TestLogin;

Step 2: Drop the Login (Orphan the User)

DROP LOGIN TestLogin;

Now, TestUser is orphaned because its server login no longer exists. You can detect it using:

USE AdventureWorks;
EXEC sp_change_users_login 'Report';

To fix it, recreate the login and map it:

CREATE LOGIN TestLogin WITH PASSWORD = 'StrongPassword!';
USE AdventureWorks;
EXEC sp_change_users_login 'Update_One', 'TestUser', 'TestLogin';

---

5. Best Practices to Avoid Orphaned Users

• Use Windows Authentication whenever possible to prevent SID mismatches.

• Script logins and permissions before migration using:

  SELECT 'CREATE LOGIN [' + name + '] WITH PASSWORD = ''YourStrongPassword'';' FROM sys.sql_logins;

• Check orphaned users after every restore to avoid permission issues.

By following these steps, you can quickly identify and fix orphaned users in SQL Server. Happy troubleshooting!