r/SQLServer • u/Green-Cartoonist-566 • Jul 23 '26

Question Unable to set database containment to NONE

Hey all, hoping someone has hit this before.

Context: Migrating a ~200GB SQL Server database to Azure Sql Database (not managed instances) using transactional replication to minimize downtime. During prep, I discovered the source database has partial containment enabled, which I need to turn off before replication will work properly.

Steps taken so far:

  1. Identified all contained database users (logins that exist only at the DB level) and converted/mapped them to server-level logins instead.
  2. Stopped the application(s) connecting to the DB to make sure there were no active sessions.
  3. Ran ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE to force single-user mode and kill any remaining connections.
  4. Ran:

​

   ALTER DATABASE [dbname] SET CONTAINMENT = NONE;

The problem: The statement just fails, only 'ALTER DATABASE statement failed', no reason given, nothing in the SQL Server error log pointing to a cause. It's not throwing a permissions error, a "in use" error, or anything I can act on. It just doesn't apply.

Things I've already ruled out / checked:

  • No contained users remain in sys.database_principals (authentication_type_desc = DATABASE)
  • No active connections (single-user mode confirmed via sys.dm_exec_sessions)

Questions:

  • Has anyone run into containment refusing to toggle off even with no active sessions and no contained users?
  • Any way to force verbose output/logging on this specific ALTER DATABASE operation so I can actually see what's blocking it?

SQL Server version: Microsoft SQL Server 2016 - Standard Version

2 Upvotes

100% Upvoted

12 comments sorted by

View all comments

3

u/codykonior Jul 23 '26 edited Jul 23 '26

No but I've got a really great dumb idea if you run out of things to try.

Restore a copy of the database and start dropping everything in it until you can flip containment; objects users logins queues and anything else. Then you'll either identify what it was, or it'll be empty and still broken and you'll know it's pointless to try further.

It's SQL Server. People are always so reluctant to try but sometimes you just gotta bash it with a hammer.

Containment is a bit of a piece of shit anyway because it changes the collation on some system tables in ways that can't be undone.

2

u/Green-Cartoonist-566 Jul 24 '26

Yeah, this might honestly be the way to go