SQL Server errors with drop login and drop user


By:   |   Updated: 2016-06-10   |   Comments (3)   |   Related: More > Security

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:

Login 'MSSQLTipsUser' owns one or more database(s). Change the owner of the database(s) before dropping the login.

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

Login 'MSSQLTipsUser' owns one or more database(s). Change the owner of the database(s) before dropping the login.

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.

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:

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:

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 database principal owns a schema in the database, and cannot be dropped.

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

The database principal owns a schema in the database, and cannot be dropped.

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:

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:

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:

The database principal owns a schema in the database, and cannot be dropped.

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 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:

Cannot drop schema 'MSSQLTipsUser' because it is being referenced by object 'CountryInfoNew'.

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:

Cannot drop schema 'MSSQLTipsUser' because it is being referenced by object 'CountryInfoNew'.

This error resulted from dropping the database schema:

Cannot drop schema 'MSSQLTipsUser' because it is being referenced by object 'CountryInfoNew'.

This error resulted from dropping the database user:

Cannot drop schema 'MSSQLTipsUser' because it is being referenced by object 'CountryInfoNew'.

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:

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


Last Updated: 2016-06-10


get scripts

next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelorís degree in computer engineering as well as .NET development experience.

View all my tips
Related Resources





Comments For This Article




Wednesday, October 24, 2018 - 3:43:26 AM - Kondwani Chipeta Back To Top (78040)

 Thank you. The part about dropping the login was helpful.

 

 

 

 

 


Saturday, June 11, 2016 - 3:16:20 AM - Ahmad Yaseen Back To Top (41662)

 Please try to drop the user from the database, then run the sp_changedbowner for that user again.

 

Regards,

Ahmad

 


Friday, June 10, 2016 - 11:17:45 AM - Rick Fitch Back To Top (41658)

 When I try to execute sp_changedbowner in one database I get this message:

Msg 15110, Level 16, State 1, Line 5

The proposed new database owner is already a user or aliased in the database.

 



download


Recommended Reading

Enabling xp_cmdshell in SQL Server

Steps to Drop an Orphan SQL Server User when it owns a Schema or Role

Encrypting passwords for use with Python and SQL Server

Is your SQL Server environment ready for GDPR?

Understanding SQL Server fixed database roles





get free sql tips
agree to terms


Learn more about SQL Server tools