Fix it now
The database already contains a user of that name, so creating one fails. After a restore onto a different server that is almost always an orphan: a database user still carrying the SID of a login on the old server, where the login here has a different one. The name matches and the identifier does not.
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';
- For each name the query returns that already has a login on this instance, relink it:
ALTER USER [appuser] WITH LOGIN = [appuser]; - Where the login does not exist here yet, create it first and then relink. For SQL logins moved between servers, create it with the original SID and nothing needs relinking at all.
- If a module fails with 15517, fix the database owner separately:
ALTER AUTHORIZATION ON DATABASE::[SalesDB] TO [sa]; - Re-run the detection query and confirm it returns nothing.
- Connect as the login and query an object it should be able to read.
Use ALTER USER, not sp_change_users_login. Microsoft documents the old procedure as deprecated and names ALTER USER … WITH LOGIN as its replacement.
If the query is empty and the login can read what it should, you are done. If users still cannot connect, the next section covers the three other errors this produces and what each one means.
Why it happens
SQL Server keeps two sets of principals. Logins live on the instance, in master, and are what you authenticate as. Users live inside each database and are what permissions are granted to. The two are joined by a security identifier rather than by name, and that identifier is stored in both places.
For a Windows login the SID comes from Active Directory, so it is the same on every server in the forest and a restored database maps straight back. For a SQL login the SID is generated by the instance that created it. Create a login called appuser on two servers and you get two different identifiers for the same name. Restore a database from the first server onto the second and the user inside it still carries the first server’s identifier, which matches nothing here. That is why Microsoft’s own detection query filters on authentication_type_desc = ‘INSTANCE’: those are the SQL-authenticated users that can orphan this way.
The visible symptoms all follow from that one mismatch, and they arrive as four different numbers. Creating the user again gives 15023, because the name is taken. Mapping a login that already has a user under another name gives 15063. Connecting or browsing gives 916, the server principal not being able to access the database under the current security context. And if the principal that went missing was the database owner, modules that execute as the owner fail with 15517.
Relinking, rather than recreating, is the whole point. The user object carries the object-level permissions, role memberships and ownership granted inside that database. ALTER USER … WITH LOGIN changes the user’s SID to match the login’s and leaves everything else intact. Dropping and recreating throws away a permission set you almost certainly have not documented.
The database was restored from another instance
You have this one if Several users in one database have SIDs matching no login on this server, and they all arrived on the same day.
- Run the detection query and note which names already have logins here.
- Relink each one:
ALTER USER [appuser] WITH LOGIN = [appuser]; - Re-run the query; it should return nothing.
ALTER USER … WITH LOGIN works for SQL users and for Windows users and groups. It cannot remap a user with no login, a user mapped to a certificate, or one mapped to an asymmetric key, and it cannot change the type of user.
The login does not exist on the new server yet
You have this one if The query returns names you have never seen on this instance, and the application cannot authenticate at all.
- For a Windows account or group, create the login from the domain identity and then relink; the SID comes from Active Directory and will be correct.
- For a SQL login, create it with the original SID and password hash taken from the source server, so no relinking is needed.
- Microsoft publishes a procedure for scripting logins with their SIDs and hashed passwords in its guidance on transferring logins and passwords between instances.
CREATE LOGIN [appuser]
WITH PASSWORD = 0x0200... HASHED,
SID = 0x1A2B3C...,
DEFAULT_DATABASE = [SalesDB],
CHECK_POLICY = OFF;
The database owner no longer resolves
You have this one if Error 15517 when running a module, or the database owner shows as a raw SID rather than a name.
- Set a valid owner:
ALTER AUTHORIZATION ON DATABASE::[SalesDB] TO [sa];This is Microsoft’s recommended remedy for an invalid dbo. - Check the whole instance at once:
SELECT name, suser_sname(owner_sid) AS owner FROM sys.databases;and look for nulls. - Choose the owner deliberately. A restored database owned by a departed administrator’s personal account is a problem waiting to recur.
This is also why ownership-chaining and TRUSTWORTHY behaviour can change after a restore: the chain is anchored to the owner, and the owner just changed.
The user owns something and refuses to be dropped
You have this one if Error 15138 when you try to remove a user you have decided you genuinely do not need.
- Read the message. It names the kind of securable the principal owns, which is not always a schema.
- Find owned schemas with
SELECT s.name FROM sys.schemas AS s JOIN sys.database_principals AS p ON s.principal_id = p.principal_id WHERE p.name = 'olduser'; - Reassign what it owns:
ALTER AUTHORIZATION ON SCHEMA::[Sales] TO [dbo]; - Then drop the user.
Full reference
Which repair applies
| What you are looking at | The repair |
|---|---|
| User exists, login of the same name exists | ALTER USER ... WITH LOGIN ... to match the SIDs |
| User exists, no login of that name | Create the login, ideally with the original SID, then relink |
| Windows user failing after a domain change | The account’s SID changed. Create the new login and relink |
| The login already has a user under a different name (15063) | Decide which user object is the one you want, and relink or drop deliberately |
| Database owner missing (15517) | ALTER AUTHORIZATION ON DATABASE::db TO [login]; |
| User cannot be dropped (15138) | It owns a securable. Reassign what the message names first |
| 916 on connecting or browsing | A security-context refusal. See below; it is not always an orphan |
Do not fix any of this by dropping the user and creating it again. The user carries the object-level permissions, role memberships and ownership granted inside that database, and dropping it discards all of them. Relinking keeps everything; recreating means rebuilding a permission set nobody wrote down.
916 is not a synonym for orphaned
Microsoft publishes 916 as the server principal not being able to access the database under the current security context, and its explanation is narrower than the usual reading. A login that can connect to the instance but has no specific permissions in a database receives the permissions of the guest user, and 916 appears when guest does not have CONNECT on that database and the database is not marked trustworthy. Its documented actions are three: grant the login access to the database, grant CONNECT to guest, or set TRUSTWORTHY on.
Only the first of those three is a routine fix. Granting CONNECT to guest opens the database to every login on the instance, and TRUSTWORTHY has consequences well beyond this error. Microsoft itself warns about circumventing the check. Where the login should have access, give it a user in the database and stop there.
The errors, precisely
| Error | What Microsoft publishes |
|---|---|
| 15023 | User, group, or role already exists in the current database. Severity 16 |
| 15063 | The login already has an account under a different user name. Severity 16 |
| 15517 | Cannot execute as the database principal because it does not exist, cannot be impersonated, or you do not have permission. Severity 16, state 1 |
| 15138 | The database principal owns a securable in the database and cannot be dropped. Severity 16 |
| 916 | The server principal is not able to access the database under the current security context |
What 15517 is really telling you
It comes from EXECUTE AS, and Microsoft lists three situations behind it: the specified principal does not exist on the server, because a login was deleted or a Windows user is no longer valid in Active Directory; the database was restored elsewhere and the dbo user’s SID in sys.database_principals no longer matches any login; or the caller lacks IMPERSONATE on the principal named. On a freshly restored database the second is almost always the one, and ALTER AUTHORIZATION on the database is the documented fix.
Doing it once instead of every restore
- Script your logins from production with their SIDs and keep the script beside your restore runbook, so a restore is followed immediately by a known-good login script.
- Create SQL logins on secondary and test servers with the production SID from the outset. Users then map on arrival and there are no orphans to chase.
- Use contained database users for databases that move often. Microsoft recommends them for exactly this: they authenticate inside the database, have no server login to lose, and cannot become orphaned.
- Fix the database owner as part of the restore, not as a separate incident three weeks later when a module fails.
Every code this article covers
| Code | What it points at | Source |
|---|---|---|
15023 |
User, group, or role already exists in the current database, so the create failed. Severity 16 | Microsoft Learn |
15063 |
The login already has an account in this database under a different user name. Severity 16 | Microsoft Learn |
15517 |
Cannot execute as the database principal, because it does not exist, cannot be impersonated, or you lack permission. Severity 16 | Microsoft Learn |
15138 |
The database principal owns a securable in the database and cannot be dropped. The message names what it owns | Microsoft Learn |
916 |
The server principal is not able to access the database under the current security context, because guest lacks CONNECT and the database is not trustworthy | Microsoft Learn |
Confirm the fix worked
- Microsoft’s detection query returns no rows in the restored database.
- Connect as the affected login and run
SELECT DB_NAME(), USER_NAME();to confirm it lands in the right database as the right user. - Query a table the user should be able to read, and confirm the permissions survived the relink.
SELECT name, suser_sname(owner_sid) FROM sys.databases;returns a resolvable owner for every database.- Run whatever module previously failed with 15517 and confirm it completes.
Questions people ask about this
Should I use sp_change_users_login instead?
No. Microsoft documents it as deprecated, says it may be removed in a future version, and names ALTER USER … WITH LOGIN as the replacement. Use ALTER USER for anything you write today.
Does this happen with Windows logins too?
Less often, because their SIDs come from Active Directory and are the same on every server in the forest. It happens when you move between domains, when an account is deleted and recreated with the same name, or when a database arrives from another organisation.
Is there a licence or edition involved in fixing this?
No. Everything here is standard T-SQL, available in every edition including the free ones. Orphaned users are a consequence of how principals are stored, not a limitation you can buy your way out of.
Can I avoid orphans altogether?
Largely. Create SQL logins on the destination with the same SID as the source, use Windows authentication, or use contained database users. Microsoft recommends the last of those specifically for databases that might be moved, because a contained user cannot become orphaned.
I got 916 rather than a login failure. Is that the same problem?
Not necessarily. 916 is the guest user lacking CONNECT on a database that is not trustworthy, so it is what a login with no user in that database gets. Give the login a user in the database rather than granting CONNECT to guest, which would open the database to everyone.
