troubleshoot-orphaned-users-sql-server.md

title	Troubleshoot Orphaned Users (SQL Server) | Microsoft Docs
ms.custom
ms.date	06/13/2017
ms.prod	sql-server-2014
ms.reviewer
ms.technology	high-availability
ms.topic	conceptual
helpviewer_keywords	orphaned users [SQL Server] logins [SQL Server], orphaned users troubleshooting [SQL Server], user accounts user accounts [SQL Server], orphaned users failover [SQL Server], managing metadata database mirroring [SQL Server], metadata users [SQL Server], orphaned
ms.assetid	11eefa97-a31f-4359-ba5b-e92328224133
author	MikeRayMSFT
ms.author	mikeray
manager	craigg

Troubleshoot Orphaned Users (SQL Server)

To log into an instance of Microsoft [!INCLUDEssNoVersion], a principal must have a valid [!INCLUDEssNoVersion] login. This login is used in the authentication process that verifies whether the principal is allowed to connect to the instance of [!INCLUDEssNoVersion]. The [!INCLUDEssNoVersion] logins on a server instance are visible in the sys.server_principals catalog view and the sys.syslogins compatibility view.

[!INCLUDEssNoVersion] logins access individual databases using a database user that is mapped to the [!INCLUDEssNoVersion] login. There are two exceptions to this rule:

• The guest account.

  This is an account that, when enabled in the database, enables [!INCLUDEssNoVersion] logins that are not mapped to a database user to enter the database as the guest user.

• Microsoft Windows group memberships.

  A [!INCLUDEssNoVersion] login created from a Windows user can enter a database if the Windows user is a member of a Windows group that is also a user in the database.

Information about the mapping of a [!INCLUDEssNoVersion] login to a database user is stored within the database. It includes the name of the database user and the SID of the corresponding [!INCLUDEssNoVersion] login. The permissions of this database user are used for authorization in the database.

A database user for which the corresponding [!INCLUDEssNoVersion] login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding [!INCLUDEssNoVersion] login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of [!INCLUDEssNoVersion]. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.

Note

A [!INCLUDEssNoVersion] login cannot access a database in which it lacks a corresponding database user unless guest is enabled in that database. For information about creating a database user account, see CREATE USER (Transact-SQL).

To Detect Orphaned Users

To detect orphaned users, execute the following Transact-SQL statements:

USE <database_name>;  
GO;   
sp_change_users_login @Action='/proxy/https/github.com/MicrosoftDocs/sql-docs/blob/e2c3003d4728ce29584144286df875611d95f6d4/docs/2014/sql-server/failover-clusters/Report';  
GO;  

The output lists the users and corresponding security identifiers (SID) in the current database that are not linked to any [!INCLUDEssNoVersion] login. For more information, see sp_change_users_login (Transact-SQL).

Note

sp_change_users_login cannot be used with [!INCLUDEssNoVersion] logins that are created from Windows.

To Resolve an Orphaned User

To resolve an orphaned user, use the following procedure:

1. The following command relinks the server login account specified by <login_name> with the database user specified by <database_user>.

   USE <database_name>;  
   GO  
   sp_change_users_login @Action='/proxy/https/github.com/MicrosoftDocs/sql-docs/blob/e2c3003d4728ce29584144286df875611d95f6d4/docs/2014/sql-server/failover-clusters/update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>';  
   GO  
   
   

   For more information, see sp_change_users_login (Transact-SQL).

2. After you run the code in the preceding step, the user can access the database. The user then can alter the password of the <login_name> login account by using the sp_password stored procedure, as follows:

   USE master   
   GO  
   sp_password @old=NULL, @new='password', @loginame='<login_name>';  
   GO  
   

   [!IMPORTANT]
   Only logins with the ALTER ANY LOGIN permission can change the password of another user's login. However, only members of the sysadmin role can modify passwords of sysadmin role members.

   [!NOTE]
   sp_password cannot be used for [!INCLUDEmsCoName] Windows accounts. Users connecting to an instance of [!INCLUDEssNoVersion] through their Windows network account are authenticated by Windows; therefore, their passwords can only be changed in Windows.

   For more information, see sp_password (Transact-SQL).

See Also

CREATE USER (Transact-SQL)
CREATE LOGIN (Transact-SQL)
sp_change_users_login (Transact-SQL)
sp_addlogin (Transact-SQL)
sp_grantlogin (Transact-SQL)
sp_password (Transact-SQL)
sys.sysusers (Transact-SQL)
sys.syslogins (Transact-SQL)