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".

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)

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".

Syntax

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

Arguments

[@Action =] ‘action‘

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

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
  SQL

  EXEC sp_change_users_login 'Report'

  

  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:
  SQL

  EXEC sp_change_users_login 'Auto_Fix', 'user'

  

  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.

Syntax

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

Arguments

• 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

• sp_change_users_login
• ALTER USER
• Troubleshoot Orphaned Users (SQL Server)