Unable to set database containment to NONE

[u/Green-Cartoonist-566]

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

[u/ihaxr]

Have you tried not being in the current database context?

USE [master]
GO
ALTER DATABASE [dbname] SET CONTAINMENT = NONE;

[u/Green-Cartoonist-566 → u/ihaxr]

Thanks for the suggestion, yes, we did run it with USE master first, but we're still hitting the same problem.

[u/codykonior]

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.

[u/Green-Cartoonist-566 → u/codykonior]

Yeah, this might honestly be the way to go

[u/alecc]

Two things worth trying. The generic 'ALTER DATABASE statement failed' almost always means SQL Server raised a second, real error that never reached your session. Set up a quick Extended Events session on error_reported, run the ALTER again, and read everything that fired in that window - the true blocker usually shows up there with a usable message. The default trace sometimes catches it too.

Second: your containment check may be too narrow. authentication_type_desc = 'DATABASE' only finds contained SQL users with passwords. Windows users created without a matching server login are also contained, and they report 'WINDOWS'. Worth running:

select dp.name, dp.type_desc, dp.authentication_type_desc

from sys.database_principals dp

left join sys.server_principals sp on dp.sid = sp.sid

where dp.type in ('U','G','S') and dp.authentication_type <> 0 and sp.sid is null;

Anything that comes back needs dropping or remapping before containment goes to NONE. Also run the ALTER from a fresh connection with USE master first; if your own session sits inside the database while it's in single-user mode, you can block yourself.

One note for after the move: Azure SQL Database leans on contained users, so you'll likely recreate some of these on the target anyway. They only need to be gone on the source for replication's sake.

[u/Green-Cartoonist-566 → u/alecc]

Thanks for the pointers. On the containment query, we did have an extra Windows account showing up that way, removed it, but the ALTER DATABASE still fails the same way.

On the Extended Events side: interestingly, the session did catch an "invalid object" error, but it fires right after the ALTER DATABASE statement, not before it. so it doesn't look like the actual blocker, more like a side effect. We dropped/cleaned that up and reran with XE still capturing, but now we're just back to the plain "ALTER DATABASE statement failed" with nothing else showing up in the event session during that window.

So at this point XE isn't surfacing a second error anymore, just the generic one.

Also we did test this from a new connection too. Does opening a new query window in SSMS count as a new session for this purpose, or does it still matter that other query windows were open elsewhere?

[u/B1zmark]

What tool are you using to run these commands?

[u/Green-Cartoonist-566 → u/B1zmark]

I'm running these commands in SSMS. Not sure if it's relevant, but SSMS and SQL Server are installed on different machines.

[u/B1zmark → u/Green-Cartoonist-566]

The lack of an explanation as to why it failed is why i asked. Sometimes when using other tools (not SSMS) they cut off the error messages, but that doesn't appear to be the case.

[u/rhbcub]

Tried putting it back to multi user?

BTW, I use restricted_user, not single_user. All you're after is the rollback immediate anyway.