When you restore a SQL Server database to a new instance, you'll often encounter orphaned users - database users that exist without corresponding server logins. This happens because:

• Logins are server-level principals stored in master database
• Users are database-level principals stored in the user database
• During restore, only the database users come across

First, let's find these orphaned users in your restored database:

USE YourDatabaseName;
GO

SELECT dp.name AS DatabaseUser, dp.type_desc AS UserType
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S','U','G') 
AND dp.name NOT IN ('dbo','guest','INFORMATION_SCHEMA','sys')
AND sp.sid IS NULL;
GO

Here are three methods to resolve this:

Method 1: Using sp_change_users_login (SQL Server 2008-2016)

EXEC sp_change_users_login 'Report'; -- First identify orphans
GO

-- For each orphaned user:
EXEC sp_change_users_login 'Auto_Fix', 'YourDatabaseUser', NULL, 'YourPassword';
GO

Method 2: CREATE LOGIN with SID (Modern Approach)

-- Get the SID for the database user
USE YourDatabaseName;
SELECT name, sid FROM sys.database_principals WHERE name = 'YourDatabaseUser';
GO

-- Create login with matching SID
USE master;
GO
CREATE LOGIN YourNewLogin 
WITH PASSWORD = 'StrongPasswordHere',
SID = 0x010500000000000903000000F15F2A...; -- Paste the SID from previous query
GO

Method 3: Script All Orphaned Users

For multiple users, generate dynamic SQL:

USE YourDatabaseName;
GO

SELECT 'CREATE LOGIN [' + dp.name + '] WITH PASSWORD = ''tempPassword'', SID = ' + 
       CONVERT(VARCHAR(100), dp.sid, 1) + ';'
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S','U','G') 
AND dp.name NOT IN ('dbo','guest','INFORMATION_SCHEMA','sys')
AND sp.sid IS NULL;
GO

• Always use strong passwords when creating logins
• For Windows authenticated users, use FROM WINDOWS instead of WITH PASSWORD
• Consider using contained databases to avoid this issue in future migrations
• After fixing, verify permissions with EXEC sp_helprotect @username = 'YourUser'

For large-scale migrations, this PowerShell script can help:

# PowerShell script to fix orphaned users
$db = "YourDatabase"
$server = "YourServer"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server($server)
$db = $srv.Databases[$db]

foreach($user in $db.Users | Where-Object {$_.Login -eq "" -and $_.IsSystemObject -eq $false}) {
    $login = New-Object Microsoft.SqlServer.Management.Smo.Login($srv, $user.Name)
    $login.LoginType = "SqlLogin"
    $login.Create("tempPassword")
    $login.Sid = $user.Sid
    $login.Alter()
}

When you restore or move a database to a new SQL Server instance, you'll often encounter a situation where database users exist but lack associated server logins. These are called "orphaned users" in SQL Server terminology. The SID (security identifier) stored in the database no longer matches any login in the new server's syslogins system table.

First, let's check which users are orphaned in a specific database:

USE YourDatabaseName;
GO

EXEC sp_change_users_login 'Report';
GO

This will return a list of database users that don't have matching logins.

The most robust method recreates logins with the same SIDs as in the original database:

-- Step 1: Get the SID for the orphaned user
USE YourDatabaseName;
GO

SELECT name, sid 
FROM sys.database_principals 
WHERE type_desc = 'SQL_USER' 
  AND authentication_type_desc = 'INSTANCE';
GO

-- Step 2: Create login with identical SID
USE master;
GO

CREATE LOGIN [YourUserName] 
WITH PASSWORD = 'StrongPasswordHere',
SID = 0x010500000000000903000000D1A9A3...; -- Paste the actual SID from previous query
GO

For simpler cases where SID matching isn't critical:

USE YourDatabaseName;
GO

-- Auto-create login with same name and default password
EXEC sp_change_users_login 'Auto_Fix', 'YourUserName', NULL, 'TempPassword123';

-- Or link to existing login
EXEC sp_change_users_login 'Update_One', 'YourUserName', 'YourLoginName';
GO

SQL Server 2012 and later versions recommend this approach:

USE YourDatabaseName;
GO

-- First create the login if it doesn't exist
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'YourLoginName')
BEGIN
    CREATE LOGIN [YourLoginName] WITH PASSWORD = 'SecurePassword123';
END
GO

-- Then map the database user to the login
ALTER USER [YourUserName] WITH LOGIN = [YourLoginName];
GO

For databases with many orphaned users, this script generates the necessary commands:

USE YourDatabaseName;
GO

SELECT 
    'USE master;' + CHAR(13) + CHAR(10) +
    'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ''' + dp.name + ''')' + CHAR(13) + CHAR(10) +
    'CREATE LOGIN [' + dp.name + '] WITH PASSWORD = ''TempPassword123'', SID = 0x' + 
    CONVERT(VARCHAR(256), dp.sid, 2) + ';' + CHAR(13) + CHAR(10) +
    'USE [' + DB_NAME() + '];' + CHAR(13) + CHAR(10) +
    'ALTER USER [' + dp.name + '] WITH LOGIN = [' + dp.name + '];'
FROM sys.database_principals dp
WHERE dp.type_desc = 'SQL_USER'
  AND dp.authentication_type_desc = 'INSTANCE'
  AND NOT EXISTS (
    SELECT 1 
    FROM sys.server_principals sp 
    WHERE sp.sid = dp.sid
  );
GO

• Always verify permissions after fixing orphaned users
• Consider resetting passwords for security
• For Windows-authenticated users, use CREATE LOGIN [domain\user] FROM WINDOWS
• Test the solution in a non-production environment first