Skip to content
«
»
 

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

Posted by 
on  June 17 2009

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:
Filed under Security Tagged , ,
Comment (RSS)  |  Permalink

17 Comments

  1. Joseph Geeprakash says:
    April 16, 2012 at 4:04 am

    Thanks a lot..This article helped us

    Reply to this comment
  2. sunil says:
    March 30, 2012 at 5:37 am

    thanks.. :-)

    Reply to this comment
  3. Adam S says:
    March 13, 2012 at 6:28 pm

    Thanks. This helped alot.

    Reply to this comment
  4. Tobey says:
    February 1, 2012 at 11:23 am

    Thank you for this post, very much appreciated!

    Reply to this comment
  5. Anonymous says:
    December 23, 2011 at 4:17 am

    Thanks a lot! :)

    Reply to this comment
  6. Anonymous says:
    December 20, 2011 at 8:00 am

    Excellent, saved me lot of time…

    Reply to this comment
  7. gudarz says:
    December 12, 2011 at 8:17 am

    Hi
    Thanks,

    Reply to this comment
  8. Mike says:
    December 5, 2011 at 9:51 am

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

    Reply to this comment
  9. Phil says:
    November 29, 2011 at 8:55 am

    Brilliant! Thanks!

    Reply to this comment
  10. Conco says:
    November 15, 2011 at 11:27 am

    Thanks, quick fix.

    Reply to this comment
  11. Lee C. says:
    October 18, 2011 at 7:54 am

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

    Reply to this comment
  12. Hemant Chandurkar says:
    October 2, 2011 at 10:41 pm

    Great! Thanks..

    Reply to this comment
  13. happy user says:
    August 7, 2011 at 8:57 pm

    great!solve my problem

    Reply to this comment
  14. SRK says:
    May 31, 2011 at 3:10 am

    Cool, thanks

    Reply to this comment
  15. EP says:
    May 30, 2011 at 4:26 am

    Excellent!! Thank you

    Reply to this comment
  16. Database User says:
    March 22, 2010 at 3:35 am

    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

    Reply to this comment

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

«
»