• Article

Troubleshooting Orphaned Users

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

SQL Server logins access individual databases using a database user that is mapped to the SQL Server login. There are two exceptions to this rule:

  • The guest account.

    This is an account that, when enabled in the database, enables SQL Server logins that are not mapped to a database user to enter the database as the guest user.

  • Microsoft Windows group memberships.

    A SQL Server 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 SQL Server login to a database user is stored within the database. It includes the name of the database user and the SID of the corresponding SQL Server login. The permissions of this database user are used for authorization in the database.

A database user for which the corresponding SQL Server 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 SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.

Note

A SQL Server 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='Report';
GO;

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

Note

sp_change_users_login cannot be used with SQL Server 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='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
    Security noteSecurity Note

    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 Microsoft Windows accounts. Users connecting to an instance of SQL Server 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).