I am trying to drop a user XYZ in SQL server. Keeps getting an erro saying drop failed

Question

I am trying to drop a user XYZ in SQL server. Keeps getting an erro saying drop failed

Miller, Zafar 0

I am trying to drop a user XYZ in SQL server. Keeps getting an erro saying drop failed msg says cannot dropt the user dbo Microsoft sql erver smo. I must be missing something.

Erland Sommarskog 137.4K Reputation points MVP Volunteer Moderator

2026-09-04T20:17:41.4733333+00:00
If Dan's suggestion does not work out, try

USE DB go DROP USER XYZ

That may still fail, but there will not be any irrelevant verbiage about SMO, but the clear error message from SQL Server.

1 answer

Your answer

Erland Sommarskog 137.4K Reputation points MVP Volunteer Moderator

2026-09-04T20:17:41.4733333+00:00

If Dan's suggestion does not work out, try

USE DB go DROP USER XYZ

That may still fail, but there will not be any irrelevant verbiage about SMO, but the clear error message from SQL Server.

Answer 1

Dan Guzman 9,521

The error may be a symptom of a mismatch between the dbo user within the database and the database owner recorded in sys.databases. Run the query below to see if the owner names are different:

USE YourDatabase;

SELECT

SUSER_SNAME(dp.sid) AS DboUserOwner

, SUSER_SNAME(d.owner_sid) LoginOwner

FROM sys.database_principals AS dp

JOIN sys.databases AS d ON d.name = DB_NAME()

WHERE dp.name = N'dbo';

To correct the problem, change the owner to a login which does not currently exist as a database user:

ALTER AUTHORIZATION ON DATABASE::YourDatabase TO SomeOtherLogin;

You should then be able to drop the XYZ user from the database.

Mariem Moalla 76 Reputation points MVP

2026-09-06T14:38:26.0166667+00:00
Also you can in this case change the owner of the database to the built-in System Administrator login in SQL Server

ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa;
Dan Guzman 9,521 Reputation points

2026-09-06T17:27:58.4566667+00:00

I agree using sa as the database owner is an option (I used to recommend the practice routinely). However, it should be done mindfully, especially when cross-database chaining is enabled.
Erland Sommarskog 137.4K Reputation points MVP Volunteer Moderator

2026-09-06T20:09:58.6133333+00:00

Having sa as the database owner is bad practice in my opinion. It can open for security holes more in one way. My recommendation is that a database should be owned by an SQL login which exists only to own that database, and which has been granted no server-level permissions whatsoever.