Skip to content
 

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

If you try to drop a user that owns a schema, you will receive the following error message:

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

In order to drop the user, you need to find the schemas they are assigned, then transfer the ownership to another user or role

SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('joe')

-- now use the names you find from the above query below in place of the SchemaName below

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo


Popular search terms:

17 Comments

  1. Joseph Geeprakash says:

    Thanks a lot..This article helped us

  2. Adam S says:

    Thanks. This helped alot.

  3. Tobey says:

    Thank you for this post, very much appreciated!

  4. Anonymous says:

    Excellent, saved me lot of time…

  5. Mike says:

    The time this saves can be measured in days, not hours!

  6. Lee C. says:

    Thanks for the helpful info, Derek. May I ask why you would alias the table name in this case?

  7. Hemant Chandurkar says:

    Great! Thanks..

  8. happy user says:

    great!solve my problem

  9. EP says:

    Excellent!! Thank you

  10. Database User says:

    I did attach database from my backup then I got the old database user come togeter. I used this note then can drop the old user.

    cheer!!!
    Thanks

post a comment OR Post Your Question on our ASK! Community!