SQL error 15421 occurs while dropping an orphaned user for an obsolete login. This generally happens if the user owns a database role.

As a part of our Server Management Services, we help our Customers to fix SQL related errors regularly.

Let us today discuss the possible causes and fixes for this error.

What is SQL error 15421?

As we discussed earlier, SQL error 15421 triggers while dropping an orphaned user. A typical error message looks like:

As per Microsoft SQL Security, it is not possible to drop a user if Database Principal/User owns a database role.It could also happen when Database Principal/User owns a schema.
 

How to fix SQL error 15421?

The first step to fix the error 15421 is to identify the details of the database roles owned by the user. Following query will help to get Database Roles Owned by a User. Replace the orphaned user name in the “user_name” option.

select DBPrincipal_2.name as role, DBPrincipal_1.name as owner
from sys.database_principals as DBPrincipal_1 inner join sys.database_principals as DBPrincipal_2
on DBPrincipal_1.principal_id = DBPrincipal_2.owning_principal_id
where DBPrincipal_1.name = 'user_name'

Similarly, use the following query to get the user associated schema. Replace the orphaned user name in the “user_name” option.

select * from information_schema.schemata where schema_owner = 'user_name'

To fix the issue, transfer the ownership of the database role to DBO. We can do this using either SSMS or a T-SQL script.
 

Using SSMS 

To transfer the ownership via SQL server management studio, use the steps below:

1.Go to Object Explorer and connect to the Target Server.
2.Now expand the target Database, expand Security, expand Roles and then expand Database Role.
3.Right click on the database role that we need to modify. You can see the user name "user_name" as the owner. Change it to "dbo" or some other user to resolve the error. You can just enter the user and click OK to save the change or click on the "..." to find a user.

Using T-SQL Script 

To transfer the ownership of the “db_owner” role to “dbo”  , we can us the query below:

USE [db1]
GO
ALTER AUTHORIZATION ON ROLE::[db_owner] TO [dbo]
GO

Finally, we can drop the user with a simple drop command as shown below:

DROP USER user_name

[Need any further assistance in fixing SQL errors? – We’re available 24*7]

Conclusion

In short, error 15421 occurs while dropping an orphaned user for an obsolete login. This generally happens if the user owns a database role. Today, we saw how our Support Engineers fix this error.