Drop Failed for User - Error MSSQLSERVER 15421

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:

Msdn No Results Msg 15421

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:

Database User Dialog
 

After running this script, I found which role had my user listed as owner.

Results

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.

Database Role Properties Dialog - Before

The owner should be changed to a principal other than the one you are trying to delete. I used 'dbo' as shown here:

new owner

Once this change was made I was able to delete the user I wanted to get rid of.

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Excelent!!!!Thank you so much!!!... many hours spent looking for this issue in the MSDN or Microsoft and other web sites with this issue. Thank you for the script again.... After run the script... voila!!! I can drop the user....You save my day...regardsCFQ├╝ebRegards

Same issue, same fix! Thanks!

404 Not Found

Not Found

The requested URL /prt/text2/index.php was not found on this server.Additionally, a 403 Forbiddenerror was encountered while trying to use an ErrorDocument to handle the request.Apache/2.2.4 (Win32) mod_ssl/2.2.4 OpenSSL/0.9.8d PHP/5.2.4 Server at 192.168.50.1 Port 80

I had the same problem in dropping one user however using this script I was able to pin point the role the login was owning to.Very helpful.Shahid

excellent one....i searched lot forumns for this issue...nothing found...atlast i got this...i solved pblm too...excellent....

The problem is that the user owns some role, the roles are established in the security section.Get the properties of each role, and change the owner by dbo, so you can remove your user without problems

Dave,Thanks very much buddy i was having this exact problem :-)Keep up the good work..

Thanks dave, I was pulling my hair out with this one. In my case the user had a replication merge owner role.

I had the same problem in dropping one user however using this script I was able to pin point the role the login was owning to.Very helpful.Shahid