SQL Server errors with drop login and drop user

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)”.  What are the steps to successfully drop SQL Server logins and users in these cases?

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 tip, 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.

How to drop a login that owns a database

The first scenario is when you try to drop a server login that is owner of a specific database. In our demonstration, we will try to drop the MSSQLTipsUser server login simply by applying the below DROP LOGIN statement:

USE [master]
GO
DROP LOGIN [MSSQLTipsUser]
GO

The statement will fail with the below error:

A similar error will appear if we try to drop it using the SSMS:

As we can clearly see from the errors, this login can’t be dropped as it is the owner of one of the databases. In order to check which database this login owns, we will query the sys.databases system table for the database owners using the below simple script:

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

From the query result below, the MSSQLTipsUser is the owner of the MSSQLTipsDemo database.

In order to make it possible to drop the login, we need to change the MSSQLTipsDemo database owner to SA (or some other user) using the sp_changedbowner system stored procedure as follows:

use MSSQLTipsDemo 
GO
sp_changedbowner 'sa'

Below we can see the owner of the MSSQLTipsDemo database has been changed to SA:

If we try to drop the login MSSQLTipsUser again, it will be dropped successfully:

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

How to drop a user that owns a schema

The second scenario, when we try to drop a database user that is owns a database schema. In our demonstration here, we will try to drop the MSSQLTipsUser database user simply by applying the below DROP USER statement:

USE [MSSQLTipsDemo]
GO
DROP USER [MSSQLTipsuser]
GO

The statement will fail getting the below error:

The same error will appear if you try to drop it using SSMS:

It is clear from the error that the MSSQLTipsuser database user can’t be dropped, as it owns one of the database schemas. To find which schema this user owns we need to browse the user in the database security node, then go to the user’s properties using SSMS. From the Owned Schemas tab of the user’s properties window, we can find that the user owns the db_owner database schema:

In order to make it possible to drop that database user, we need to change the owner of the db_owner schema to another user, which will be dbo in our case. To achieve this, we will use the ALTER AUTHORIZATION ON SCHEMA T-SQL statement as follows:

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

If we try to drop the database user again, it will be dropped successfully:

How to drop a user that owns a schema and the schema is referenced by an object

The last scenario, when we try to drop a database user that owns a database schema and also this schema is referenced by a database object. In our demonstration here, if we will try to drop the MSSQLTipsUser database user simply by applying the below DROP USER statement:

USE [MSSQLTipsDemo]
GO
DROP USER [MSSQLTipsuser]
GO

The statement will fail getting the below error:

If we try to get the schema that is owned by this user from the user’s properties, it is found that this user owns the MSSQLTipsuser schema:

If we try to drop this schema using the DROP SCHEMA T-SQL statement below:

USE [MSSQLTipsDemo]
GO
DROP SCHEMA [MSSQLTipsUser]
GO

The query will fail getting the below error:

As we can see from this error, this schema is used by the CountryInfoNew table.

The same two errors will be shown if we try to drop the user using SSMS. As you can see from these errors, SQL Server tried first to drop the schema then the user:

This error resulted from dropping the database schema:

This error resulted from dropping the database user:

In order to resolve this issue, we will change the schema of the CountryInfoNew table in order to break any reference to that schema using the sp_changeobjectowner system stored procedure as below:

sp_changeobjectowner 'MSSQLTipsuser.CountryInfoNew','dbo'

Now if we try to drop the schema again, it will be dropped successfully, as it is not referenced by any object:

USE [MSSQLTipsDemo]
GO
DROP SCHEMA [MSSQLTipsuser]
GO

We can now drop the database user as it does not own any schemas:

Next Steps

• Check out more SQL Server Security Tips