DB-유

Contained Databases and Contained Users in SQL Server

· mssql, security, migration

I'm tired of fixing orphaned users. You move a database to another server and spend the next hour on it: the users came along with the backup, the logins didn't, and the SIDs no longer match. Contained databases fix this at the source. A partially contained database stores the user's password inside the database itself, so there's no server login to lose. Restore anywhere and the users can log in immediately.

They've existed since SQL Server 2012, work in every edition, and are underused. You need sysadmin for the server-level setting and ALTER ANY DATABASE for the database change. Also know which applications connect, because their connection strings will need the database name.

Server setting first

It's off by default:

EXEC sp_configure 'contained database authentication', 1;
RECONFIGURE;

Do this on every server the database might be restored to, including Availability Group replicas.

Set the database to partial containment

ALTER DATABASE Sales SET CONTAINMENT = PARTIAL;

PARTIAL is the only containment level besides NONE; a fully contained mode was announced but never shipped. The change requires no downtime, though the statement needs to briefly take an exclusive lock, so do it in a quiet moment.

Contained users

A contained SQL user is created with a password and no login:

USE Sales;
CREATE USER app_sales WITH PASSWORD = 'Long-random-password-here!', DEFAULT_SCHEMA = dbo;
ALTER ROLE db_datareader ADD MEMBER app_sales;
ALTER ROLE db_datawriter ADD MEMBER app_sales;

A contained Windows user is created directly from the domain account, again without a login:

CREATE USER [CONTOSO\sales_reporting] ;
ALTER ROLE db_datareader ADD MEMBER [CONTOSO\sales_reporting];

Both authenticate at the database, not at the instance. They have no server-level identity and cannot see other databases, sys.dm_exec_requests for other sessions, or anything else that requires a login.

Converting existing users

For a database that already has login-based users, sp_migrate_user_to_contained copies the password hash from the login into the database user:

EXEC sp_migrate_user_to_contained
    @username = N'app_sales',
    @rename = N'keep_name',
    @disablelogin = N'disable_login';

@disablelogin = 'disable_login' disables the server login afterwards so you can confirm nothing still depends on it before dropping it. I never drop the login on day one. Run this for each SQL user in the database:

SELECT dp.name
FROM sys.database_principals dp
JOIN sys.server_principals sp ON sp.sid = dp.sid
WHERE dp.type = 'S' AND dp.authentication_type = 1;   -- INSTANCE-authenticated SQL users

Connection strings change

A contained user is only known inside its database, so the connection must specify the database. Without Initial Catalog, SQL Server tries to authenticate against master and the login fails.

Server=sql01.contoso.local;Database=Sales;User ID=app_sales;Password=...;

For sqlcmd, that is the -d switch: sqlcmd -S sql01 -d Sales -U app_sales -P ....

Checking the result

Check the containment status and how each user authenticates:

SELECT name, containment_desc FROM sys.databases WHERE name = 'Sales';

SELECT name, type_desc, authentication_type_desc
FROM sys.database_principals
WHERE type IN ('S', 'U') AND principal_id > 4;

authentication_type_desc shows DATABASE for contained users, INSTANCE for login-based ones, and WINDOWS for contained Windows users.

Then test the portability: back up the database, restore it under another name (or on another server with the server setting enabled) and connect as app_sales to the restored copy. No sp_change_users_login, no ALTER USER ... WITH LOGIN.

Gotchas

  • Msg 12824: The sp_configure value 'contained database authentication' must be set to 1 in order to alter a contained database. Run the sp_configure step on this server.
  • Msg 12809: You must remove all users with password before setting the containment property to NONE. You are trying to un-contain a database that still has contained users. Convert them back to login-based users first or drop them.
  • Login failed for user 'app_sales'. (Error 18456, State 5) from an application that used to work. The connection string does not specify the database, so authentication was attempted against master. Add Initial Catalog.
  • Msg 12829: The contained database cannot be enabled because ... contains uncontained entities. Not an error you will see on ALTER DATABASE, but sys.dm_db_uncontained_entities lists objects that reference things outside the database (three-part names, linked servers, msdb procedures). These still work in a partially contained database; the view is only informational.
  • Collation surprises in temp tables. In a contained database, temporary objects and metadata use the catalog collation Latin1_General_100_CI_AS_KS_WS_SC rather than the tempdb collation. If your code joins temp tables to varchar columns with a different collation, you may need explicit COLLATE clauses.

For anything restored regularly (dev refreshes, log-shipped copies, failovers) this is worth the small effort. Since I switched, orphaned users are one less thing on the migration checklist.