A database restore brings database users with it, but server-level logins live in master. After restoring on another instance, a user can exist inside the database while its matching login is missing or has a different SID.
Last updated: September 26, 2026.
USE AppDatabase;
GO
SELECT dp.name AS database_user,
dp.sid,
sp.name AS server_login
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp
ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U', 'G')
AND dp.principal_id > 4
AND sp.sid IS NULL;Run the inventory before changing mappings. Exclude deliberate contained users, certificate users, and accounts that no longer need access.
Map the user to the correct login
If the server login already exists, remap the database user with ALTER USER [AppUser] WITH LOGIN = [AppLogin];. Do not drop and recreate the user unless necessary; doing so can disturb database permissions and ownership. The broader orphaned-user guide covers bulk diagnosis.
If the login is missing, recreate or transfer it with the original SID. SQL authentication also requires its password hash to be transferred securely; never paste plaintext production passwords into scripts or documentation. The login-transfer procedure explains SID preservation.
Check error 18456 details
Read the SQL Server error log for the state associated with error 18456. A default database that is offline or missing can cause login failure before the user reaches the restored database. Temporarily set a valid default database, then correct the application connection string. Microsoft’s error 18456 reference maps common states to causes.
Verify the final security path
- Connect using the application’s actual authentication method.
- Confirm the login maps to the intended database user.
- Review role membership and explicit grants; do not grant db_owner as a shortcut.
- Test the application’s read and write operations with least privilege.
When planning the restore itself, use the checks in restoring a backup to another server.
