A restored database can be online while its users cannot map to the intended destination logins. Confirm the actual server, error and identity before changing access. Compare SQL-user SIDs and, after an AD-domain change, Windows identities separately; an empty SQL-orphan query does not prove application access is correct.
This guide uses SQL Server 2022 on Windows as its reference. Its query is read-only, source-researched and untested. No accounts or permissions were changed. It focuses on SQL identity mapping after recovery; domain authentication and Kerberos require the separate companion investigation.
Locate the failed access boundary
Preserve the application error, destination name, intended database and failure time. Ask the DBA to correlate that attempt with the SQL error log. A generic message shown to an end user may omit the distinction between rejected authentication and inability to open a database.
Confirm the application reached the intended SQL instance. A stale alias or connection string can direct it to the original server, a different named instance or another environment. A successful administrator connection to the new server does not prove the application used that destination.
Record the actual authentication mode. A SQL login, a Windows service account, a Windows group and a contained database user do not have identical dependency paths. Identify the identity used by the running application service, not merely the account used by the administrator opening its configuration screen.
Keep passwords and full connection strings out of the evidence report. Record aliases and non-secret settings sufficient to reproduce the intended path. If a connection string embeds credentials, refer to its approved secret location rather than copying it into a support ticket.
Understand what database restoration carries
A database contains its database users and permissions. Instance-level login state is a separate dependency. Microsoft’s login-transfer guidance explains why moving or restoring a database can require migration of logins and their SIDs as well.
Names are not enough. A login created on the destination with the same spelling can have a different SID from the database user restored from the source. The visible names can therefore look correct while the mapping is wrong.
Conversely, not every missing match in a rough identity comparison establishes an application fault. The authentication type matters, and Windows access may be granted through a group. A contained user follows a different model. Use Microsoft’s scoped detection method and then connect each finding to the application’s actual identity.
Also inventory instance-level dependencies beyond the first login: SQL Agent job ownership, credentials, linked servers and any other features the application actually uses. Fixing the initial connection is not proof that scheduled or cross-server work will succeed. Do not copy every source-server principal indiscriminately.
Read the mapping evidence
The following detection query follows the pattern in Microsoft’s orphaned-user documentation. An authorised DBA should run it in the specific restored user database, after confirming the connection and database context. It has not been executed for this article.
SELECT dp.type_desc, dp.sid, dp.name AS user_name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp
ON dp.sid = sp.sid
WHERE sp.sid IS NULL
AND dp.authentication_type_desc = 'INSTANCE';
The result identifies instance-authenticated database principals without a matching visible server principal under this method. It is not a universal audit of every Windows group membership or effective permission. Have the DBA use adequate approved metadata visibility and interpret the identity type before declaring an account orphaned.
For the affected application identity, compare the source mapping, if safely available, with the restored user and intended destination login. Record the database user name, authentication type and SID relationship in a protected diagnostic record. The public article needs no real account names.
Inspect whether the destination login is enabled and which database it opens by default. Check that the application explicitly selects the intended database where its supported configuration permits that. Microsoft’s documented transfer method does not transfer the login’s default database; review and set it separately through the approved change S2.
An empty query result narrows this particular hypothesis. It does not establish that the password, domain path, login policy, database state or object permissions are correct. Follow the error evidence to the next boundary instead of changing permissions experimentally.
Check the Windows-domain case and the actual error
The preceding INSTANCE-filtered query covers SQL users authenticated through instance logins. Microsoft notes that a move to another AD domain can also orphan Windows users because new-domain identities have different SIDs S2. Untested, read-only inventory in the affected database:
SELECT name, type_desc, authentication_type_desc, sid
FROM sys.database_principals
WHERE type IN ('U', 'G');
These are Windows users and groups R1. Have the authorised DBA/directory owner compare them with the intended destination principals and group access. A missing direct login is not by itself proof of broken Windows access: group-based paths and metadata visibility matter. Seeing all users requires the documented metadata permissions; do not grant rights merely to make the list look complete.
Read the SQL error log for the same failed attempt, including its reason and state. Microsoft's error 18456 reference distinguishes, for example, state 8 (password mismatch), states 38/46 (requested database not found), and state 58 (SQL authentication against a Windows-only server, or a SID mismatch). These guide investigation, not an automatic permission change R2.
When the original SQL login is lost, Microsoft documents recreating a SQL login with the required SID, as well as migrating an existing login or remapping with ALTER USER ... WITH LOGIN S1. The DBA must establish the intended identity and credentials before any of those changes.
Choose between preserving identity and remapping
If the destination lacks a login that should retain the source identity, the DBA can use the documented login migration procedure. Review the generated definitions, SID preservation, authentication type, compatibility and intended rights before applying them S2.
Treat generated login scripts as sensitive. They can contain password hashes or other authentication material even when they do not show a readable password. Store and transfer them through the approved recovery process, not in the article folder, chat transcript or a general source repository.
If the correct destination login already exists and the database user should belong to it, Microsoft documents ALTER USER ... WITH LOGIN as the mapping mechanism. The older sp_change_users_login procedure is deprecated S1. The mapping decision must identify the intended person or service, not just make two names agree.
Do not automatically drop and recreate the database user. That can complicate ownership and permission relationships which should be preserved and understood. Likewise, do not create a new login for every Windows group member when the supported design grants access through the group.
Contained database users may reduce reliance on an instance login in a supported design, but converting an existing application to contained authentication is a separate architecture change. It is not the emergency default for a failed restore login.
This article withholds executable account-creation and remapping statements. They grant or redirect access. Have the DBA review the exact source and destination identities, expected permissions and reversal before making the change.
Preserve the intended permission boundary
Record the application’s required operations before testing the remedy. It may need to read certain tables and execute specified procedures without being able to administer the instance or read another customer’s database.
After the identity mapping is corrected, compare the database roles, explicit permissions and object ownership with the approved application design. A permission error after successful authentication is a different finding from an orphaned user. Do not treat it as justification for sysadmin or unrestricted database ownership.
If the application previously depended on excessive privileges, document that separately. A recovery incident is not a safe setting for an unplanned permissions redesign, but it also does not justify silently granting broader rights than the established requirement.
Retain a specific reversal plan. Returning a mapping to its prior login can itself affect access, and disabling a newly created login can break another dependency if it was reused during testing. Limit the change to named objects and reconcile dependencies before reversal.
Test from the real application context
Start with the application’s supported connection test under its actual service identity. A successful connection made interactively by a domain administrator exercises a different security context.
Use isolated synthetic data to verify a representative read and an allowed write or procedure execution. Confirm the result appears in the intended restored database. Where the application offers an environment identifier, check it rather than relying solely on a familiar screen layout.
Include negative tests that fit the permission design. The application identity should remain unable to access a deliberately out-of-scope lab database or perform an unauthorised administrative action. Do not run destructive negative tests against production to prove denial.
Exercise scheduled work separately. A background job may use a different owner, proxy or credential from the interactive application. The companion scheduled-jobs article covers safe activation and duplicate-work prevention; this article checks that identity dependencies are recorded and tested.
If Kerberos or Windows authentication still fails, retain the corrected SQL mapping evidence and move to that specific authentication investigation. Repeating user remapping cannot fix a wrong service principal name or an unreachable domain dependency.
Complete the recovery record
Record the observed symptom, identity mismatch or other established cause, exact approved change, application results and access-denial results. Distinguish “database online”, “login authenticated” and “application workflow accepted”. Each is useful evidence with a different scope.
Keep the corrected login and credential dependencies in the recovery manifest so that the next restore does not rediscover them. Verify that the plan covers loss of the original instance, not merely a database move while the source administrators remain available.
No successful application result is claimed here. These are proposed checks for a DBA-reviewed rehearsal. Production activation still needs the application owner’s acceptance and a clear decision about which restored system is authoritative.
Limitations
The examples are SQL Server 2022 inventory, not a complete authentication audit. Contained users, Windows group membership and permission-filtered catalogues need separate interpretation. No login creation, grants or remapping were executed.
Acceptance checklist
- Failed attempt correlated to the intended instance, database and SQL error-log reason.
- SQL SIDs and relevant Windows-domain identities checked separately.
- Default database and required permissions reviewed without broad elevation.
- Application identity passed its intended operation and negative access checks.
Learning notes
- The same login name does not guarantee the same SID.
- A restored database carries users, but instance-level authentication dependencies need separate attention.
- Correct access includes both permitted application work and retained restrictions.
Related reading: SQL Kerberos After Migration: Check SPNs and NTLM Use; Restore a TDE Database Without the Original SQL Server; Scheduled Jobs After Migration: Prevent Duplicate Work.
Sources and review status
Sources accessed 30 October 2026. Exact displayed dates, scope and claim mapping are in the accompanying evidence file. Revised after independent review; independent recheck pending. All examples remain untested and no lab validation is claimed.
- Troubleshoot Orphaned Users - SQL Server Always On | Microsoft Learn
- Transfer SQL Server Logins and Passwords Between Instances - SQL Server | Microsoft Learn
Additional revision sources: