The database principal owns a schema in the database, and cannot be dropped

Expert User Verified

As per Microsoft SQL Security you cannot drop a user in one of the below scenarios: Database Principal/User owns a database role Database Principal/User owns a schema

alter authorization
on schema::YourSchemaName
to dbo
go

drop user TheUserYouWantToDelete
go

In this query you can get user schema as a result for AdventureWorks database:

USE AdventureWorks;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('your username');

after take schema name you can alter authorization on schema like this:

ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;

Comments

Leave a Comment