Click here to Skip to main content
12,503,679 members (53,699 online)
Click here to Skip to main content
Add your own
alternative version

Stats

60.2K views
14 bookmarked
Posted

How to Fix Orphaned SQL Users

, 26 May 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
Recently I had to involve in a database migration task. I backed up the database and restored successfully in the new server. However when I access the database through the web site, database login didn’t work.

Recently I had to involve in a database migration task. I backed up the database and restored successfully in the new server. However when I access 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".

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)

User login information in syslogins table in the master database

 

User information in the sysusers table in the user database

See above example, both tables contain the different SID 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".

Syntax

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

Arguments

[@Action =] ‘action

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

ValueDescription
Auto_FixLinks 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.
ReportLists 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_OneLinks 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'
Lists the orphaned users
  • If you already create a SQL server with same login information and if you want to map that with the database user
EXEC sp_change_users_login 'Auto_Fix', 'user'
Auto fix orphaned user
  1. 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.

Syntax

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

Argument

  • 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.
ALTER USER UserName WITH LOGIN = UserName
Alter user

For more information

sp_change_users_login

ALTER USER

Troubleshoot Orphaned Users (SQL Server)

License

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

Share

About the Author

Tharaka MTR
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

You may also be interested in...

Pro
Pro

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
Mihai MOGA13-Jun-13 20:48
memberMihai 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.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160919.1 | Last Updated 27 May 2013
Article Copyright 2013 by Tharaka MTR
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid