Click here to Skip to main content
15,440,323 members
Articles / Web Development / ASP.NET
Technical Blog
Posted 17 May 2013


16 bookmarked

How to Fix Orphaned SQL Users

Rate me:
Please Sign up or sign in to vote.
5.00/5 (9 votes)
26 May 2013CPOL3 min read
This post will show you how to fix orphaned SQL users.

Recently, I had to get involved in a database migration task. I backed up the database and restored successfully in the new server. However, when I accessed the database through the web site, database login didn’t work. The problem was even though the database user is included in the restored database; the login information was not there. So I re-created the login in the server, but it also didn’t work.

This phenomenon is called "orphaned users".

Image 1

Details of the Problem

User logon information is stored in the syslogins table in the master database. By changing servers, or by altering this information by rebuilding or restoring an old version of the master database, the information may be different from when the user database dump was created. If logons do not exist for the users, they will receive an error indicating "Login failed" while attempting to log on to the server. If the user logons do exist, but the SID (secure identifier) in master..syslogins and the sysusers table in the user database differ, the users may have different permissions than expected in the user database. (Microsoft et al)

Image 2

User login information in syslogins table in the master database

Image 3

User information in the sysusers table in the user database

See the above example, both tables contain different SIDs for the username "eyepax".

How to Fix

  1. The easiest way to fix this is delete the user from the restored database and then create and setup the user & corresponding permission to the database.
  2. If the user owns a schema in the database, you won’t be able to delete the user. Then, you can use the special stored procedure "sp_change_users_login".


sp_change_users_login [ @Action = ] ‘action‘
[ , [ @UserNamePattern = ] ‘user‘ ]
[ , [ @LoginName = ] ‘login‘ ]
[ , [ @Password = ] ‘password‘ ]


[@Action =] ‘action

Following are the list of actions that can be performed by the procedure.

Value Description
Auto_Fix Links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins. You should check the result from theAuto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.
When using Auto_Fix, you must specify user and password; login must be NULL. user must be a valid user in the current database.
Report Lists the users and corresponding security identifiers (SID) in the current database that are not linked to any login.
user, login, and password must be NULL or not specified.
Update_One Links the specified user in the current database to login. login must already exist. user and login must be specified. password must be NULL or not specified.

To perform the operations, first you have to select the database which contained the issues. Here are some of the operations you can perform.

  • Lists the orphaned users
    EXEC sp_change_users_login 'Report'

    Image 4

    Lists the orphaned users
  • If you already create a SQL server with the same login information and if you want to map that with the database user:
    EXEC sp_change_users_login 'Auto_Fix', 'user'

    Image 5

    Auto fix orphaned user
  • MSDN says, maps an existing database user to a SQL Server login. sp_change_users_login feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER USER instead.


WITH <set_item> [ ,...n ]
<set_item> ::=
NAME = newUserName
| DEFAULT_SCHEMA = schemaName
| LOGIN = loginName


  • userName -Specifies the name by which the user is identified inside this database.
  • LOGIN =loginName - Re-maps a user to another login by changing the user’s Security Identifier (SID) to match the login’s SID.
  • NAME =newUserName - Specifies the new name for this user. newUserName must not already occur in the current database.
  • DEFAULT_SCHEMA =schemaName - Specifies the first schema that will be searched by the server when it resolves the names of objects for this user.

Image 6

Alter user

For More Information


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Technical Lead Eyepax IT Consulting (Pvt) Ltd.
Sri Lanka Sri Lanka
Having more than 9 year hands-on industry experience in software development
Responsible for designing, implementing and managing complex software systems with stringent up-time requirement.

Visit my blog

Comments and Discussions

Generalsp_change_users_login Pin
Ryan Pringnitz21-Oct-13 3:22
MemberRyan Pringnitz21-Oct-13 3:22 
GeneralRe: sp_change_users_login Pin
Tharaka MTR24-Oct-13 20:22
professionalTharaka MTR24-Oct-13 20:22 
GeneralMy vote of 5 Pin
Ștefan-Mihai MOGA13-Jun-13 20:48
professionalȘtefan-Mihai MOGA13-Jun-13 20:48 
GeneralRe: My vote of 5 Pin
Tharaka MTR16-Jun-13 4:50
professionalTharaka MTR16-Jun-13 4:50 
GeneralMy vote of 5 Pin
Nick Ginis28-May-13 2:20
professionalNick Ginis28-May-13 2:20 
GeneralRe: My vote of 5 Pin
Tharaka MTR28-May-13 6:34
professionalTharaka MTR28-May-13 6:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.