A few days ago, I conduct an audit to detect all orphan’s windows accounts in a database and I was surprise to have an error during the drop user query.
The first step is to find all orphan’s windows accounts in a database
USE [dbi_database] GO /*Step1: Search the orphan user */ SELECT * FROM sys.database_principals a LEFT OUTER JOIN sys.server_principals b ON a.sid = b.sid WHERE b.sid IS NULL AND a.type In ('U', 'G') AND a.principal_id > 4
I find the user called “dbi\orphan_user” and run the query to drop it
/*Drop Orphran User*/ DROP USER [dbi\orphan_user] GO
But as you can see, I receive the error message:
Msg 15421, Level 16, State 1, Line4
“The database principal owns a database role and cannot be dropped.”
This user is owner of database roles…
Be careful it is not this error message:
Msg 15138, Level 16, State 1, Line 4
The database principal owns a schema in the database, and cannot be dropped.
In this case, the user is owner on schema.
Do not confuse these two error messages:
- Msg 15421 is for database role
- Msg 15138 is for schema
The goal is to search all database roles owns by the user dbi\orphan_user
/*Search database role onws by this Orphran user*/ 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 = 'dbi\orphan_user';
As you can see in my select, I use two times the view sys.database_principals to do a cross check between the owning_principal_id and the principal_id.
After that, I change the owner from this role to the good one (by default dbo).
/*Change the owner from these database role*/ ALTER AUTHORIZATION ON ROLE::<database role> TO dbo;
And I drop the orphan user without problems…
/*Drop Orphran User*/ DROP USER [dbi\orphan_user] GO
To finish, I give you a Santa Klaus Gift:
I also rewrite the query to have the “Alter Authorization” query directly in the SELECT. You have just to copy/paste and execute it
SELECT dp2.name, dp1.name, 'ALTER AUTHORIZATION ON ROLE::' + dp1.name + ' TO dbo;' as query 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 = 'dbi\orphan_user';
Et voila! 😎