How to drop a SQL Server user with db owner privilege

Question

I need to drop a user with dbowner schema from a SQL Server database. I cannot drop it as it is since I get this error message

Drop failed for User 'network service'. (Microsoft.SqlServer.Smo)

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

When I try to uncheck the schema owned by this user to remove the DB owner it does nothing. My question is how I can drop this user or edit its name from 'network service' to 'NT AUTHORITY\NETWORK SERVICE'

Answer 1

I had the same problem, I run two scripts then my problem is solved.

Try this:

In this query you can get user schema as a result for AdventureWorks database:

USE AdventureWorks;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('your username');

after take schema name you can alter authorization on schema like this:

ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;

in this query db_owner schema name that get from first query.

finally you can delete user without error.

my source: SQL SERVER – Fix: Error: 15138

Answer 2

I have the same issue, I cannot delete the user

foo

since it says:

So I need to go to Security -> Schemas and look for dbo, then right click and choose Properties:

Then change

foo

to

dbo

then after that I can now delete the user that I want to delete.

Answer 3

In my case I execute these commands and problem solved:

ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_datareader TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_datawriter TO dbo;

Answer 4

ALTER AUTHORIZATION ON SCHEMA::[NT AUTHORITY\SYSTEM] TO dbo