Using Logins vs. Contained Users in SQL Server: Choosing the Right Authentication Approach

Introduction

SQL Server provides two primary methods for user authentication: traditional logins and contained users. Choosing the right approach is critical for database administrators and architects when designing secure, scalable, and maintainable systems.

This blog explains both authentication methods, highlights their pros and cons, and guides you in selecting the right one based on your specific use case.

---

Traditional Logins: The Established Approach

What Are Logins?

Logins are server-level principals that authenticate users at the instance level. To access a specific database, a login must be mapped to a database user.

-- Creating a traditional SQL Server login
CREATE LOGIN [AppUser] WITH PASSWORD = 'Str0ngP@ssw0rd!';

-- Mapping the login to a database user
USE [YourDatabase];
CREATE USER [AppUser] FOR LOGIN [AppUser];

Advantages of Traditional Logins

1. Centralized user management at the server level

2. Familiar and widely adopted by DBAs

3. Seamless integration with Active Directory (Windows Authentication)

4. Visibility through system views like sys.server_principals

Disadvantages of Traditional Logins

1. Databases are less portable due to login dependencies

2. Common issues with orphaned users after database restoration

3. Disaster recovery becomes more complex due to login recreation requirements

---

Contained Users: The Modern Alternative

What Are Contained Users?

Contained users, introduced in SQL Server 2012, are database-level users with credentials stored inside the database. They are not linked to server-level logins, making them self-contained and portable.

-- Enabling contained databases
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'contained database authentication', 1;
RECONFIGURE;

-- Creating a contained user
USE [YourDatabase];
CREATE USER [ContainedUser] WITH PASSWORD = 'Str0ngP@ssw0rd!';

Advantages of Contained Users

1. Portability — users move with the database during backup or migration

2. Simplified user management — no mapping to server-level logins

3. Reduces server-level attack surface

4. Ideal for Always On Availability Groups and multi-tenant designs

Disadvantages of Contained Users

1. Credentials are stored in the database, not ideal for all security policies

2. No support for Windows Authentication or Active Directory integration

3. Same usernames can exist in different databases, leading to potential conflicts

4. Less familiar to DBAs who use traditional models

---

Key Differences at a Glance

Feature	Traditional Logins	Contained Users
Scope	Server-level	Database-level
Authentication Info	Stored in master DB	Stored in user DB
AD Integration	Supported	Not supported
Portability	Manual recreation required	Moves with database
Orphaned Users	Possible	Avoided
Visibility	sys.server_principals	sys.database_principals

---

When to Use Each Approach

Use Traditional Logins When:

1. Integration with Active Directory is required

2. You manage a small number of databases per server

3. Working with legacy applications

4. Server-wide permissions and access are needed

5. Your team is already using this model

Use Contained Users When:

1. Portability is a priority (cloud, test/dev environments)

2. You manage many databases with the same user structure

3. Supporting a multi-tenant environment

4. Working with Always On Availability Groups

5. Simplifying disaster recovery and migrations

---

Security Considerations

For Traditional Logins:

• Regularly audit for orphaned users:

  EXEC sp_change_users_login 'Report';
  

• Enforce strong password and account policies

• Use roles for permission management, not individual grants

For Contained Users:

• Avoid duplicate usernames across multiple databases

• Apply robust password policies at the database level

• Use roles and schemas to manage access securely

• Monitor and audit contained user access

---

Migration Strategy

Converting Traditional Logins to Contained Users

1. Identify the logins to be converted:

   SELECT name FROM sys.server_principals WHERE type_desc = 'SQL_LOGIN';
   

2. For each login, create a contained user:

   USE [YourDatabase];
   CREATE USER [ContainedUser] WITH PASSWORD = '...', SID = <login_sid>;
   

3. Transfer permissions as needed:

   EXEC sp_helpuser '[OldLoginUser]';
   

---

Best Practices for Mixed Environments

1. Document which databases use which authentication model

2. Adopt a naming convention to differentiate user types

3. Monitor both server-level and database-level principals

4. Train teams on managing both contained and traditional users

---

Performance Considerations

• Authentication performance is generally comparable

• Contained users may experience a slight overhead due to user lookup at the database level

• Traditional logins may benefit from server-level connection pooling

• Contained users reduce complexity by eliminating the need for mapping logins to users

---

Conclusion

Choosing between traditional logins and contained users depends on your environment and goals:

• For portability, DevOps pipelines, and simplified migrations, contained users are ideal

• For Active Directory integration and centralized control, traditional logins remain essential

Many organizations successfully use a hybrid approach, leveraging the strengths of both models—using traditional logins for administrative roles and contained users for application-specific access.

The best choice is the one that aligns with your architecture, security requirements, and operational workflows.