Assigning Logins to Orphaned Users in SQL Server: A Comprehensive Guide

The Login-User Separation in SQL Server Security Architecture

In SQL Server's security model, logins and users represent distinct but related concepts. A login is a server-level security principal used for authentication and server instance access, while a user is a database-level security principal employed for authorization and specific database access. This separation enables flexible permission management and multi-database access control.

When encountering error message "Msg 916, Level 14, State 1" during database connection attempts, it indicates that the server principal cannot access the target database under the current security context. This typically occurs when a database user lacks an associated login, creating what's known as an "orphaned user." Orphaned users may result from database migration, login deletion, or creation without specified logins.

System View Analysis and SID Identification

To diagnose user status, first query the sys.database_principals system view:

SELECT name, type_desc, sid, authentication_type_desc 
FROM sys.database_principals 
WHERE name = 'UserName';

This query returns the user's Security Identifier (SID), which is crucial for resolving orphaned user issues. The SID is a unique identifier in Windows security architecture that SQL Server uses to associate logins with users.

It's important to note that Windows logins and SQL logins handle SIDs differently:

• Windows login SIDs originate from Active Directory with fixed formats and lengths
• SQL login SIDs are generated by SQL Server as 16-byte binary values

When obtaining SIDs from query results, their types must be distinguished. The SUSER_SNAME() function can verify the Windows login name corresponding to a SID:

SELECT SUSER_SNAME(0x0105000000000009030000001139F53436663A4CA5B9D5D067A02390);

Solution: Creating Matching Logins

For orphaned users of SQL login type, the solution involves creating a new login with the same SID. First, obtain the correct binary SID value from sys.database_principals:

-- Retrieve user's binary SID
DECLARE @UserSID VARBINARY(85);
SELECT @UserSID = sid 
FROM sys.database_principals 
WHERE name = 'UserName' AND type = 'S';

-- Create login with identical SID
CREATE LOGIN [UserName] 
WITH PASSWORD = N'StrongPassword123!', 
DEFAULT_DATABASE = [DatabaseName], 
CHECK_EXPIRATION = OFF, 
CHECK_POLICY = OFF, 
SID = @UserSID;

The key point is ensuring the SID parameter is a 16-byte binary value. If encountering "Msg 15419" error, it indicates incorrect SID format. The proper SID should come from the sys.database_principals.sid column, which stores VARBINARY(85) data, though SQL logins only require the first 16 bytes.

User Remapping and Permission Preservation

After creating the login, use the ALTER USER statement to re-establish the association:

USE [DatabaseName];
ALTER USER [UserName] 
WITH LOGIN = [UserName];

The primary advantage of this approach is preserving all existing user permissions. Since SIDs match, SQL Server recognizes this as the same security principal, avoiding reconfiguration of complex permission structures.

Alternative Approaches and Compatibility Considerations

Although the sp_change_users_login stored procedure traditionally handled orphaned users, Microsoft has marked it as deprecated:

-- Deprecated method
EXEC sp_change_users_login 'Auto_Fix', 'UserName', NULL, 'Password';

In newer SQL Server versions, the ALTER USER method is recommended. However, sp_change_users_login might still be used in legacy systems or specific scenarios. The procedure offers a 'Report' option to identify orphaned users:

EXEC sp_change_users_login 'Report';

Special Handling for Windows Logins

For Windows login type users, the approach is simpler. When the corresponding Windows account exists, SQL Server typically automatically re-establishes associations. If automatic repair fails, manually create the Windows login:

CREATE LOGIN [Domain\UserName] 
FROM WINDOWS;

Windows logins don't require SID parameters since SQL Server retrieves correct SIDs from Active Directory.

Security Best Practices

When handling login-user mappings, consider these security practices:

1. Implement strong password policies even with CHECK_POLICY set to OFF
2. Regularly audit database user status to promptly identify orphaned users
3. Validate solutions in test environments before implementing in production
4. Document all security configuration changes for auditing and troubleshooting
5. Consider using contained databases to reduce login-user dependencies

Troubleshooting and Verification

After completing repairs, verify solution effectiveness:

-- Verify user status
SELECT dp.name AS UserName, sp.name AS LoginName, dp.sid
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.name = 'UserName';

-- Test connection
EXECUTE AS USER = 'UserName';
SELECT SUSER_NAME(), USER_NAME();
REVERT;

If verification fails, check these common issues:

• SID mismatch: Ensure identical SIDs for login and user
• Permission problems: Current operator needs ALTER ANY USER permission
• Database context: Ensure operations in correct database
• Login conflicts: Ensure no existing login with same name

Conclusion and Extended Applications

Properly addressing orphaned user issues in SQL Server requires deep understanding of its security architecture. Through systematic analysis of user types, correct SID retrieval, matching login creation, and user remapping, access problems can be efficiently resolved while preserving existing permissions. This approach applies not only to individual database repairs but also extends to batch processing of multiple orphaned users or integration into database migration and disaster recovery processes.

As SQL Server versions evolve, security features continue to advance. In SQL Server 2016 and later, contained database functionality offers alternative user authentication management, reducing dependencies on traditional login-user models. However, for most existing systems, mastering the techniques described in this article remains essential.