Problem: I was trying to cleanup my SQL Server logins and users, but when I tried to drop a login I got this error message “Login ‘x’ owns one or more database(s). Change the owner of the database(s) before dropping the login.”. Also, when I tried to drop a database user I got this error message “The database principal owns a schema in the database, and cannot be dropped. (error 15138)”.

Solution: There are two access levels in SQL Server; access at the server level is granted via SQL Server or Windows Authentication logins, and access at the database level is granted via database users. The server login is also mapped to a database user in order to grant access at the database level. In this blog post, we will concentrate on how to resolve cases in which you may not be able to drop the server login or the database user as it is connected to a specific database or server object.

Login ‘x’ owns one or more database(s). Change the owner of the database(s) before dropping the login.

The first scenario is when you try to drop a server login that is the owner of a specific database. In order to drop the login, you need to change the owner of the database to another user. Here are the steps:

USE [master]
GO
DROP LOGIN [MSSQLTipsUser]
GO

The above statement will fail with the error message “Login ‘MSSQLTipsUser’ owns one or more database(s). Change the owner of the database(s) before dropping the login.” To check which database the login owns, you can query the sys.databases system table:

SELECT name, suser_sname(owner_sid) AS DBOwner FROM sys.databases

From the query result, you can identify the database that the login owns. To change the owner of the database, you can use the sp_changedbowner system stored procedure:

USE [MSSQLTipsDemo]
GO
sp_changedbowner 'sa'

After changing the owner of the database, you can successfully drop the login.

The database principal owns a schema in the database, and cannot be dropped. (error 15138)

The second scenario is when you try to drop a database user that owns a database schema. In order to drop the user, you need to change the owner of the schema to another user. Here are the steps:

USE [MSSQLTipsDemo]
GO
DROP USER [MSSQLTipsuser]
GO

The above statement will fail with the error message “The database principal owns a schema in the database, and cannot be dropped.” To find which schema the user owns, you can browse the user in the database security node and go to the user’s properties using SSMS. From the Owned Schemas tab, you can identify the schema owned by the user.

To change the owner of the schema, you can use the ALTER AUTHORIZATION ON SCHEMA T-SQL statement:

USE [MSSQLTipsDemo]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [DBO]
GO

After changing the owner of the schema, you can successfully drop the database user.

The database principal owns a schema in the database, and cannot be dropped. (error 15138) and the schema is referenced by an object

The last scenario is when you try to drop a database user that owns a database schema and this schema is referenced by a database object. In order to drop the user, you need to change the schema of the object to break the reference. Here are the steps:

USE [MSSQLTipsDemo]
GO
DROP USER [MSSQLTipsuser]
GO

The above statement will fail with the error message “The database principal owns a schema in the database, and cannot be dropped.” To find the schema owned by the user, you can check the user’s properties in SSMS.

To change the schema of the object, you can use the sp_changeobjectowner system stored procedure:

sp_changeobjectowner 'MSSQLTipsuser.CountryInfoNew','dbo'

After changing the schema of the object, you can successfully drop the schema and the database user.

Conclusion: Dropping SQL Server logins and users can be challenging when they are connected to specific databases or server objects. By following the steps outlined in this blog post, you can successfully drop logins and users in these cases.