Microsoft error 15421 is usually triggered in the process of dropping an orphaned user for an obsolete login. This is common when the user of the Database owns a schema or database role.

As part of our Server Support Services, we have helped our customers resolve numerous  SQL issues.

Here in this context we shall look into why this error occurs and how to fix it.

More about SQL Error 15421

As we have already stated, Error 15421 takes place when a user is being dropped. Based on the security measure set by Microsoft, a user cannot be dropped directly in cases where the database's user owns a database role or a schema. Therefore there are important steps which needs to be considered before dropping of the user can be successful.

How to solve SQL Error 15421

To get a rid of error 15421 it is important to know the actual database roles owned by the user in question.

You can use the sql query below to get the database roles which the user owns. In here replace the name of user as per your case just as we used  "name_of_user";

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 = 'name_of_user'

To get the database schema owned by the user, you can use the sql script below;

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

There are different approaches on how to resolve this error and we are going to consider the possible ways of fixing it.

Our goal is to transfer the ownership of the database role or Schema to DBO. Below we will make use of T-SQL Script or SSMS.

i. Via T-SQL Script

This is the most easiest method. This involves transferring ownership of the "db_owner" role to "dbo".

The following sql query can be used;

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

After running the script, you can proceed with dropping the user with the command below;

DROP USER name_of_user

ii. Via SSMS

To change the database role via SSMS, First open the Object Explorer and connect to the Target Server. Next, expand the target database, the security , Roles, and Database Roles respectively. Then right click on the database role you want to change. Here you will see the username. In our case it is "name_of_user". You can change it to "dbo" to fix this error.

Do you need technical support in solving SQL errors? Reach us Today.