Here at IbmiMedia, We have seen an increasing support request on how to resolve SQL related issues as part of our Server Support Services.

In this context, We will look into the main causes of this error and how to fix it.

Why does SQL server error 15138 occurs?

The SQL error 15138 takes place when a database user is about to be dropped. It relates to a database user having some schemas at the database level.

How to fix SQL server error 15138

As this is related to schemas, a starting point to fix this issue is to change the owner of the affected schema before dropping the user.

The steps taken to achieve this is provided below;

i. Troubleshoot and identify the schemas owned by the user

ii. Move the ownership of the schema.

iii. Finally, drop the user of the schema.

We shall explain briefly these processes below.

Troubleshooting and Identifying the Schemas owned by the user

It is important to know who the owner of the Schemas which the user of the database to be dropped is. You can take the following steps to complete this process;

1. Make a connection to the SQL Server using the SQL Server Management Studio.

2. Now open the database folder close to the actual database name.

3. Next Open the Security folder of the affected database and then the Users folder to get the list of database users.

4. Do a right-click on the user and select the Properties.

5. You will see Owned Schemas in the left side of the page containing a list of schemas. The ticked ones belongs to the user.

Alternatively you can use the SQL query box to locate the Schemas owned by the user via the command below;

USE Name_of_DB
GO
SELECT s.name SchemaName
FROM sys.schemas
WHERE s.principal_id = USER_ID('abc')

Here the name of the database is represented by "Name_of_DB" while the owner is the user "abc". Thus use yours according to your situation.

Moving the ownership of the schema

Once the schemas which the user owns is identified, the ownership can be transferred to other users.

In this case, you can use the ALTER AUTHORIZATION command. This command helps to change the ownership.

You can use the following query command to do this;

USE [Name_of_DB]
G0
Alter Authorization ON Schema::[Name_of_SCHEMA] TO [dbo]
Go

In this case, the name of the database is represented by "Name_of_DB" while Schema name  is represented by "Name_of_SCHEMA".

This process can also be done by using the SQL Server Management Studio. In the same way as we have used in the previous steps, locate the Schema in the database folder.

Additionally to drop the user, you can use the query below;

USE [Name_of_DB]
Go
DROP USER USERNAME
Go

Need support in solving SQL errors? Let us know.