By: Ahmad Yaseen | Comments (4) | Related: > 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:
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
About the author
Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
