In a SQL Server 2005 database, I was having a hard time deleting a user I had created.
I kept getting this error:
The database principal owns a database role and cannot be dropped. Msg 15421.
MSDN and Google were not helpful on this error. Ater lots of search attempts I kept coming up empty:
I spent some time looking through various dialogs in SQL Server Management Studio. I was unable to find the problem - probably because I am not as familiar with the UI as I was with Enterprise Manager.
I finally wrote a script that helped me identify for which role the user was listed as an owner. Here it is:
select dp2.name as role, dp1.name as owner
from sys.database_principals as dp1 inner join sys.database_principals as dp2
on dp1.principal_id = dp2.owning_principal_id
where dp1.name = 'DeleteMe'
In the last line of the script, make sure you specifiy the user name and not the login name. 'DeleteMe' is the user name I want to delete. See the screen shot below:
After running this script, I found which role had my user listed as owner.
With that knowledge, I opened the role dialog in SQL Server Management Studio and changed the owner to 'dbo'. Below is the before screen shot.
The owner should be changed to a principal other than the one you are trying to delete. I used 'dbo' as shown here:
Once this change was made I was able to delete the user I wanted to get rid of.