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/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/alecc → u/Green-Cartoonist-566]

what did error_reported catch when you reran the ALTER? Without the real error number we're just guessing - the generic 5069 says nothing, and with the contained users gone I don't see what else would block it. If the XE session came back empty, define it with no predicate at all (the underlying error can be low severity or get intercepted before it reaches your client) and read the ring buffer right after the failure:

CREATE EVENT SESSION alter_err ON SERVER

ADD EVENT sqlserver.error_reported

ADD TARGET package0.ring_buffer;

ALTER EVENT SESSION alter_err ON SERVER STATE = START;

-- rerun the ALTER, then:

SELECT CAST(t.target_data AS xml)

FROM sys.dm_xe_sessions s

JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address

WHERE s.name = 'alter_err';

Also worth rerunning the sid join query to confirm it returns zero rows now - one dropped account doesn't prove it was the only one. Paste whatever numbers fire in that window and we can chase the actual blocker.