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:

[code]
The database principal owns a schema in the database, and cannot be dropped.
[/code]

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

18 comments
Arpan Shah 09 Jul 2013 at 4:09 pm

This is really very helpful

Thanks a lot

Joseph Geeprakash 16 Apr 2012 at 4:04 am

Thanks a lot..This article helped us

sunil 30 Mar 2012 at 5:37 am
Adam S 13 Mar 2012 at 6:28 pm

Thanks. This helped alot.

Tobey 01 Feb 2012 at 11:23 am

Thank you for this post, very much appreciated!

Anonymous 23 Dec 2011 at 4:17 am

Thanks a lot! :)

Anonymous 20 Dec 2011 at 8:00 am

Excellent, saved me lot of time…

gudarz 12 Dec 2011 at 8:17 am
Mike 05 Dec 2011 at 9:51 am

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

Phil 29 Nov 2011 at 8:55 am

Brilliant! Thanks!

Conco 15 Nov 2011 at 11:27 am

Thanks, quick fix.

Lee C. 18 Oct 2011 at 7:54 am

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

Hemant Chandurkar 02 Oct 2011 at 10:41 pm

Great! Thanks..

happy user 07 Aug 2011 at 8:57 pm

great!solve my problem

SRK 31 May 2011 at 3:10 am

Cool, thanks

EP 30 May 2011 at 4:26 am

Excellent!! Thank you

Database User 22 Mar 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

Derek D. 07 Apr 2010 at 8:20 am

Cool. In using the schemas, I’ve had to use this a lot myself. I finally memorized it. (after looking it up hundreds of times)..

Featured Articles

 Site Author