SQL error 15138 triggers while dropping a database user that owns some schemas at the database level.

As a part of our Server Management Services, we help our Customers to fix SQL related errors regularly.

Let us today discuss the possible causes and fixes for this error.

What is SQL error 15138?

As we discussed earlier the SQL error 15138 occurs while dropping a database user. It generally happens when the user owns some schemas at the database level. A typical error message would look like:

Changing the owner of the identified schema prior to dropping the user can fix it. Let us now look at the steps to perform this task.

What is SQL error 15138?

The steps to resolve the SQL error 15138 includes the following processes:

1. Identify the schemas owned by the user
2. Transfer the ownership of the schema
3. Drop the User

Lets us now look at each of these in detail.

Identify the schemas owned by the user

First process to resolve the 15138 error is to identify the schemas that the user to be deleted owns. With the series of steps below, we can check the schema that a user owns in the database.

1. Connect to SQL Server Instance in SQL Server Management Studio.
2. Expand the database folder followed by the actual database name in which the user exists or created.
3. Expand Security folder inside the target database followed by the Users folder and we will find a list of database users.
4. Right-click on the user and select Properties.
5. Select Owned Schemas in the left pane of the window. A list of schemas will be displayed. The user owns the ones which are ticked.

Another option is to query schema names from sys.schemas table for the user. Here is the query:

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

Change DBName with the database name which is owned by the user “xyz”.

Transfer the ownership of the schema

Once we have identified the schemas that are owned by the user, the next step is to transfer its ownership to some other user.

We can change the owner of the schema using the ALTER AUTHORIZATION command. This command can be used to change the ownership of any securable that has an owner. We transfer the ownership to dbo, which is a type of user account in SQL Server that has permissions to perform all activities in the database. We can drop the user after changing the ownership.

To change the owner of the schema, execute the query given below:

USE [DBName]
G0
Alter Authorization ON Schema::[SCHEMA_NAME] TO [dbo]
Go

Change DBName with the database name and Schema_Name with the schema name that the user owns. This can also be done using SQL Server Management Studio. The steps to be followed for it include:

1. Connect to SQL Server Instance in SQL Server Management Studio.
2. Expand the database folder followed by the actual database name in which the user exists or created.
3. Expand Security folder inside the target database followed by the Schemas folder.
4. Right-click on the schema that has to be modified. We can see the user “xyz” as the owner. Change it to “dbo” or some other user to resolve the error. We can just enter the user and click OK to save the change or use Search to find a user.

Drop the user

As we have removed the user from the ownership of the schema, we can finally proceed to drop the user. Run below command to drop the user.

USE [DBName]
Go
DROP USER USERNAME
Go

Change DBName with the database name where the user exists and USERNAME with the user name which you want to drop.

We can perform this from SQL Server Management Studio as well by right-clicking on the user and choose the delete option.

1. Connect to target SQL Server Instance.
2. Expand the Database folder in which the user exists.
3. Expand the Security folder and then Users folder to get the target user name.
4. Right-click and choose delete on the identified user which needs to be deleted.
5. Click on the Ok button of the user deletion window.

[Need any further assistance in fixing SQL errors? – We’re available 24*7]

Conclusion

In short, SQL error 15138  occurs while dropping a user. This happens mainly when the user owns a schema at the database level. Today, we saw how our Support Engineers fix this error.