The database principal owns a database role and cannot be dropped. Msg 15421, Level 16
MSSQL SQL Server Error 15421Problem Description:
Usually we face this issue while dropping user from database which owns database roles and error code 15421 indicates issue of database role ownership which need to be modified.
Solution:
Run below query on database to check database roles owned by that user which need to be removed.
select dp2.name,dp1.name from sys.database_principals as dp1 join sys.database_principals as dp2 on dp1.owning_principal_id=dp2.principal_id where dp1.type='R' and dp2.name='<username>'
get the role name from above query and pass it to below query.
use <dbname>
go
alter authorization on role::[rolename] to [dbo]
go
once rolename authorization is changed to dbo, you can now drop the user from the database.
use <dbname>
go
exec sp_dropuser <username>
go
How useful was this post?
Click on a star to rate it!
Average rating 5 / 5. Vote count: 2
No votes so far! Be the first to rate this post.