How to Fix Orphaned SQL Users


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

Database with 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 login information in syslogins table in the master database

User information in the sysusers table in the user 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.

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'
Lists the orphaned users

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

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.

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

Alter user

For more information

sp_change_users_login
http://msdn.microsoft.com/en-us/library/ms174378(v=sql.110).aspx

ALTER USER
http://msdn.microsoft.com/en-us/library/ms176060.aspx

Troubleshoot Orphaned Users (SQL Server)
http://msdn.microsoft.com/en-us/library/ms175475.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s