Managing Orphaned Users in SQL Server: A Comprehensive Guide

Managing orphaned users in SQL Server is critical to database security and administration. Orphaned users occur when a database user exists without a corresponding login in the master database. This mismatch can disrupt access and lead to potential security vulnerabilities. In this article, we'll explore what orphaned users are, how to detect them, and methods to resolve them, including updated examples and scenarios.

Background

To connect to an SQL Server database, a user must have a valid login in the master database. This login authenticates the user and maps to a database user for authorization. The mapping is maintained using the Security Identifier (SID), ensuring that database users and server logins are linked correctly.

Exceptions to the rule of login-user mapping include:

1. Contained Database Users: Authenticate at the database level, eliminating dependency on server logins. These users enhance database portability but require separate recreation for each database.
2. Guest Accounts: Enabled by default, they allow access to users without explicit mapping but are generally discouraged for security reasons.
3. Microsoft Windows Group Memberships: Windows group members can access databases if the group is added as users.

A database user becomes orphaned when:

• The corresponding login is deleted.
• The database is migrated or restored without the associated logins.
• The SID mapping between the user and login is incorrect.

What Are Orphaned Users?

An orphaned user occurs when:

• The database user exists, but the corresponding SQL Server login in the master database does not.
• The database user SID and login SID mismatch.

These scenarios disrupt authentication and authorization, leading to errors or restricted access.

Detecting Orphaned Users

For SQL Server

Use the following query to identify orphaned users:

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 dp.authentication_type_desc = 'INSTANCE';

For Azure SQL Database or Synapse Analytics

1. Retrieve the SIDs of logins in the master database:

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

2. Retrieve SIDs of users in the target database:

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 lists to identify mismatches.

Resolving Orphaned Users

1. Recreate Missing Login with SID 

If the login was deleted:

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

2. Map an Orphaned User to an Existing Login 

If the login exists but isn't mapped:

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

3. Change User Password 

After recreating the login, update its password:

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

Using sp_FindOrphanedUser

The stored procedure sp_FindOrphanedUser is a powerful tool for detecting and resolving orphaned users. It provides:

1. A list of orphaned users.
2. Auto-generated scripts for repairs.
3. Details about schemas owned by orphaned users.

Get the package code from sp_FindOrphanedUser.

Running the Procedure

Default execution:

EXEC sp_FindOrphanedUser;

For a specific database:

EXEC sp_FindOrphanedUser @DatabaseName = '<DatabaseName>';

Examples of Orphaned User Scenarios

Example 1: User Without Login

Scenario

A login is deleted, leaving the database user orphaned.

• Detect the orphaned user:
  MS SQL

   

  EXEC sp_FindOrphanedUser;

  
  

• Recreate the login:
  MS SQL

   

  CREATE LOGIN [User1] WITH PASSWORD = 'SecurePassword123';
  USE [ExampleDB];
  ALTER USER [User1] WITH LOGIN = [User1];

  
  

Example 2: SID Mismatch

Scenario

The login SID doesn't match the user SID.

• Detect the mismatch:
  MS SQL

   

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

  
  

• Correct the SID mapping:
  MS SQL

   

  USE [ExampleDB];
  EXEC sp_change_users_login 'UPDATE_ONE', 'User2', 'User2';

  
  

Example 3: Orphaned User Owning a Schema

Scenario

Orphaned user prevents schema deletion.

• Detect schema ownership:
  MS SQL

   

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

  
  

• Reassign schema ownership:
  MS SQL

   

  USE [ExampleDB];
  ALTER AUTHORIZATION ON SCHEMA::[User3Schema] TO [dbo];
  DROP USER [User3];

  
  

Best Practices

• Use Contained Database Users: Avoid dependency on server logins.
• Synchronize Logins: Always migrate or restore logins during database migrations.
• Regular Audits: Periodically check for orphaned users to maintain security.

Conclusion

Managing orphaned users ensures database security and operational efficiency. By using queries and tools like sp_FindOrphanedUser, you can quickly identify and resolve issues, maintaining seamless access for legitimate users. Adopting best practices like contained users and regular audits can prevent such problems in the future.