19

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.

26

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;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.