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”.
sp_change_users_login [ @Action = ] ‘action‘
[ , [ @UserNamePattern = ] ‘user‘ ]
[ , [ @LoginName = ] ‘login‘ ]
[ , [ @Password = ] ‘password‘ ]
[@Action =] ‘action‘
Following are the list of actions can be performed by the procedure.
||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.
||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.
||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.
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
3. 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.
ALTER USER userName
WITH <set_item> [ ,…n ]
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.
ALTER USER UserName WITH LOGIN = UserName
For more information
Troubleshoot Orphaned Users (SQL Server)