The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

If you drop a user that owns a schema, you’ll receive the following error:

Drop failed for User ‘my_user’.  (Microsoft.SqlServer.Smo)
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

so, to drop user, you have to find which schemas assigned to, and then transfer the ownership to another user (or role).

To find assegned schema, enjoy this snippet:

SELECT sc.name
FROM sys.schemas sc
WHERE sc.principal_id = USER_ID('my_user')

Once found schema (for example db_datareader), use it to transfer ownership with this snippet:

ALTER AUTHORIZATION
ON SCHEMA::db_datareader
TO dbo

Se provi ad eliminare un utente che appartiene a uno schema, riceverai l’errore seguente:

Drop failed for User ‘my_user’.  (Microsoft.SqlServer.Smo)
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

dunque, per eliminare l’utente, bisogna trovare lo schema a cui appartiene, e trasferire l’ownership ad un altro utente (o ruolo).

Per trovare lo schema a cui appartiene l’utente utilizza il seguente snippet:

SELECT sc.name
FROM sys.schemas sc
WHERE sc.principal_id = USER_ID('my_user')

Una volta trovato lo schema con la query precedente (per esempio db_datareader), utilizzarlo per trasferire l’ownership con questo snippet:

ALTER AUTHORIZATION
ON SCHEMA::db_datareader
TO dbo