Drop Failed for User – Error MSSQLSERVER 15421

This ‘how-to’ holds for TWO error messages…

1. The database principal owns a database role and cannot be dropped.

2. The database principal owns a schema and cannot be dropped.

Solution:

1. Open SQL Server Management Studio and login as admin.

2. Click on “New Query” button on the top left corner.

3. Select the problematic DB from the drop-down menu on the left of the “Execute” button just above the text-input area.

4. Paste this in the query input box:

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’

5. Change the “DeleteMe” name with the user you want to delete. (e.g. where dp1.name = ‘Fadi’)

6. Click “Execute” now.

7. You will get a list of roles in which the user exists.

8. Now open Databases -> yourTargetDatabase -> Security -> Roles -> Database Roles.

9. Now double click the entries that were listed in the output of the above SQL command.

10. Change the “Owner” to some temp username (e.g. I used ‘dbo’ [without the quotes] when I was trying to fix my problem).

11. If the username you want to delete appears in the box below it, select and remove it from there too.

12. Do this for all the Roles that came up in the above SQL query.

13. Navigate to Databases -> yourTargetDatabase -> Security -> Schemas.

14. Double-click to open “db_owner” and change the schema owner to dbo.

15. Now goto : Databases -> yourTargetDatabase -> Security -> Users.

16. Right click the username you want to delete and click “Delete”, then click OK in the new box that appears.

17. If it still gives an error… this tutorial wasn’t for you 😀

If the error keeps showing up leave a comment and I’ll help you through it… had an annoying time with that error myself 🙁 Hope it helps you !!

Cheers.