20

I've accidentally given a user the ownership of the db_owner schema (using the check box in the UI as below) and now I cannot:

  1. Transfer ownership to another user
  2. Drop the user from DB (though I can delete login in the SQL Server)

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

ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo

And while it completed successfully, the user still has ownership, and it's greyed out so I can't seem to do it in the UI either.

enter image description here

Found a solution:

In addition to Arron's answer, I realised that I'd run the above command in wrong DB (facepalm!). Once the DB had been corrected both the above SQL and the answer below worked.

Preet Sangha
  • 907
  • 4
  • 11
  • 22

1 Answers1

28

Slightly different approach would be to give the schema back to the original owner/schema, instead of to dbo:

ALTER AUTHORIZATION ON SCHEMA::db_owner TO db_owner;
Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624