draft version of mapping user s and logins in sql server

 

When you restore a SQL Server database from a backup, logins associated with database users may become orphaned if they are not mapped to the correct server logins. Here’s how you can fix orphaned logins:

---

Step 1: Identify Orphaned Users

Run the following query in the restored database to find orphaned users:

sql
USE [YourDatabase];
GO
EXEC sp_change_users_login 'Report';
GO

This will return a list of orphaned users.

---

Step 2: Fix Orphaned Logins

You can fix the orphaned logins in one of two ways:

A. If the Login Exists in the Server but is Not Mapped

Run the following command to map the user back to the login:

sql

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

✅ This will automatically map the orphaned user to the existing login with the same name.

---

B. If the Login Does Not Exist on the Server

If the login doesn’t exist on the server, create it first:

sql

CREATE LOGIN YourLoginName WITH PASSWORD = 'YourStrongPassword';
GO

Then, manually map it:

sql

USE [YourDatabase];
GO
EXEC sp_change_users_login 'Update_One', 'YourOrphanedUser', 'YourLoginName';
GO

---

Step 3: Verify the Fix

After running the above commands, rerun:

sql

EXEC sp_change_users_login 'Report';

If no rows are returned, the issue is fixed.

---

Alternative: Use ALTER USER (For SQL Server 2012+)

Instead of sp_change_users_login, you can also use:

sql

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

---

Best Practices

1. Use Windows Authentication when possible to avoid orphaned logins.
2. Always check orphaned users after a database restore.
3. Consider scripting logins and permissions before migration using:

   sql
   
   SELECT 'CREATE LOGIN [' + name + '] WITH PASSWORD = ''YourStrongPassword'';'
   FROM sys.sql_logins
   WHERE name NOT LIKE '##%'
   

Would you like help with scripting login migrations before restore? 🚀

2. Restore the Database:

After downloading, restore the AdventureWorks database to your SQL Server instance.

3. Simulate Orphaned Users:

To create orphaned users, follow these steps:

• Create a Login and User:

  sql
  -- Create a new login
  CREATE LOGIN TestLogin WITH PASSWORD = 'StrongPassword!';
  
  -- Create a user in the AdventureWorks database associated with the login
  USE AdventureWorks;
  CREATE USER TestUser FOR LOGIN TestLogin;
  

• Drop the Login:

  sql
  
  -- Drop the login, which will orphan the user in the database
  DROP LOGIN TestLogin;
  

Now, TestUser in the AdventureWorks database is orphaned because its associated server-level login (TestLogin) has been removed.

4. Identify and Resolve Orphaned Users:

To detect and fix orphaned users, you can use the following commands:

• Identify Orphaned Users:

  sql
  USE AdventureWorks;
  EXEC sp_change_users_login 'Report';
  

• Fix the Orphaned User:

  sql
  -- Recreate the login
  CREATE LOGIN TestLogin WITH PASSWORD = 'StrongPassword!';
  
  -- Associate the orphaned user with the new login
  USE AdventureWorks;
  EXEC sp_change_users_login 'Update_One', 'TestUser', 'TestLogin';
  

By following these steps, you can simulate an environment with orphaned users and practice resolving them.

For more detailed information on managing orphaned users, refer to Microsoft's official documentation:

• Troubleshoot Orphaned Users (SQL Server):
  learn.microsoft.com

This resource provides comprehensive guidance on identifying and resolving orphaned users in SQL Server.

In SQL Server, you can check the SID (Security Identifier) of a user or login using system views. The SID is used to uniquely identify a login and its associated database user.

---

1. Check SID of a Server Login (From sys.server_principals)

To find the SID of a server-level login, run:

sql
SELECT name, sid 
FROM sys.server_principals
WHERE type IN ('S', 'U', 'G'); -- S = SQL Login, U = Windows Login, G = Windows Group

• SQL Server logins will have a binary SID.
• Windows logins (AD users) will have a longer binary SID.

For a specific login:

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

---

2. Check SID of a Database User (From sys.database_principals)

To find the SID of a database user:

sql
USE YourDatabase;
SELECT name, sid 
FROM sys.database_principals
WHERE type IN ('S', 'U', 'G'); -- S = SQL user, U = Windows user, G = Windows group

For a specific user:

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

---

3. Match Database User to Login SID (To Identify Orphaned Users)

If a database user is orphaned (i.e., its SID doesn’t match any login), use:

sql
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. Convert SID to Readable Hexadecimal Format

Since sid is stored as VARBINARY(85), you can convert it to a readable hexadecimal format:

sql

SELECT name, CONVERT(NVARCHAR(100), sid, 1) AS SID_Hex 
FROM sys.server_principals
WHERE name = 'YourLoginName';

Would you like help with fixing orphaned users based on SID mismatches? 🚀