Add to collection
Expert User

Expert User

Verified

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

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

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;
alter authorization
on schema::YourSchemaName
to dbo
go

drop user TheUserYouWantToDelete
go

Comments 0

  • Sorry!! No comment posted yet. Become the first user to give comment.
Add a comment

Would you like to write for w3 sniff?

Become part of an amazing group of contributors and authors and start writing for us

Join Now
alter authorization
on schema::YourSchemaName
to dbo
go

drop user TheUserYouWantToDelete
go
Feedback submitted successfully.