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 script 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.