Skip to content

troubleshoot-orphaned-users-sql-server.md

Latest commit

 

History

History
134 lines (100 loc) · 9.14 KB

troubleshoot-orphaned-users-sql-server.md

File metadata and controls

134 lines (100 loc) · 9.14 KB
title Troubleshoot Orphaned Users (SQL Server) | Microsoft Docs
ms.custom
ms.date 07/14/2016
ms.prod sql-server-2016
ms.reviewer
ms.suite
ms.technology
dbe-high-availability
ms.tgt_pltfrm
ms.topic article
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
caps.latest.revision 41
ms.author mikeray
manager jhubbard

Troubleshoot Orphaned Users (SQL Server)

[!INCLUDEtsql-appliesto-ss2008-all_md]

Orphaned users in [!INCLUDEssNoVersion] occur when a database user is based on a login in the master database, but the login no longer exists in master. This can occur when the login is deleted, or when the database is moved to another server where the login does not exist. This topic describes how to find orphaned users, and remap them to logins.

Note

Reduce the possibility of orphaned users by using contained database users for databases that might be moved. For more information, see Contained Database Users - Making Your Database Portable.

Background

To connect to a database on an instance of [!INCLUDEssNoVersion] using a security principal (database user identity) based on a login, the principal must have a valid login in the master database. This login is used in the authentication process that verifies the principals identity and determines if 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.sql_logins compatibility view.

[!INCLUDEssNoVersion] logins access individual databases as "database user" that is mapped to the [!INCLUDEssNoVersion] login. There are three exceptions to this rule:

  • Contained database users

    Contained database users authenticate at the user-database level and are not associated with logins. This is recommended because the databases are more portable and contained database users cannot become orphaned. However they must be recreated for each database. This might be impractical in an environment with many databases.

  • The guest account.

    When enabled in the database, this account permits [!INCLUDEssNoVersion] logins that are not mapped to a database user to enter the database as the guest user. The guest account is disabled by default.

  • 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 applied for authorization in the database.

A database user (based on a login) 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. Orphaning can happen if the database user is mapped to a login SID that is not present in the master instance. A database user can become orphaned after a database is restored or attached to a different instance of [!INCLUDEssNoVersion] where the login was never created. A database user can also become orphaned if the corresponding [!INCLUDEssNoVersion] login is dropped. Even if the login is recreated, it will have a different SID, so the database user will still be orphaned.

To Detect Orphaned Users

For [!INCLUDEssNoVersion] and PDW

To detect orphaned users in [!INCLUDEssNoVersion] based on missing [!INCLUDEssNoVersion] authentication logins, execute the following statement in the user database:

SELECT dp.type_desc, dp.SID, dp.name AS user_name  
FROM sys.database_principals AS dp  
LEFT JOIN sys.server_principals AS sp  
    ON dp.SID = sp.SID  
WHERE sp.SID IS NULL  
    AND authentication_type_desc = 'INSTANCE';

The output lists the [!INCLUDEssNoVersion] authentication users and corresponding security identifiers (SID) in the current database that are not linked to any [!INCLUDEssNoVersion] login.

For SQL Database and SQL Data Warehouse

The sys.server_principals table is not available in SQL Database or SQL Data Warehouse. Identify orphaned users in those environments with the following steps:

  1. Connect to the master database and select the SID's for the logins with the following query:

    SELECT sid 
FROM sys.sql_logins 
WHERE type = 'S';

  2. Connect to the user database and review the SID's of the users in the sys.database_principals table, by using the following query:

    SELECT name, sid, principal_id
FROM sys.database_principals 
WHERE type = 'S' 
  AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')
  AND authentication_type_desc = 'INSTANCE';

  3. Compare the two lists to determine if there are user SID's in the user database sys.database_principals table which are not matched by login SID's in the master database sql_logins table.

To Resolve an Orphaned User

In the master database, use the CREATE LOGIN statement with the SID option to recreate a missing login, providing the SID of the database user obtained in the previous section:

CREATE LOGIN <login_name>   
WITH PASSWORD = '<use_a_strong_password_here>',  
SID = <SID>;

To map an orphaned user to a login which already exists in master, execute the ALTER USER statement in the user database, specifying the login name.

ALTER USER <user_name> WITH Login = <login_name>;

When you recreate a missing login, the user can access the database using the password provided. Then the user can alter the password of the login account by using the ALTER LOGIN statement.

ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';

Important

Any login can change it's own password. 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.

The deprecated procedure sp_change_users_login also works with orphaned users. sp_change_users_login cannot be used with [!INCLUDEssSDS].

See Also

CREATE LOGIN (Transact-SQL)
ALTER USER (Transact-SQL)
CREATE USER (Transact-SQL)
sys.database_principals (Transact-SQL)
sys.server_principals (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.sql_logins sys.syslogins (Transact-SQL)