Ms sql ошибка 15138

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.

You’re cleaning up some old usernames in a database. The users are no longer needed so you want to drop them and maybe even the server login. You issue the standard DROP USER username; command in the query editor and it immediately comes back with Msg 15138, Level 16, State 1, Line 1 The database principal owns a schema in the database, and cannot be dropped.

The error message is telling us that the user owns a schema in this database. And as long as that’s the case, the user cannot be dropped. So, we need to determine which schema(s) are owned by the user.

Who owns a schema? Finding the reason for Msg 15138

There are a couple of easy ways to determine which schemas a user owns. You can view properties window for the user, or you can write a quick query. Let’s look at each method.

Viewing Owned Schemas in the Properties window

To see which schemas a user owns, drill down in the server, database, Security, Users in the connections pane of SQL Server Management Studio or Azure Data Studio. Right-click the user and select Properties. You’ll see the following Database User properties window open. Click on the Owned Schemas page.

In this example, Kim owns the db_ddladmin schema. That’s preventing you from dropping the user.

Using T-SQL to see the schemas owned by a user

Not really a point-and-click kind of person? The following query will show you a list of the schemas owned by a specific user. Of course, you can omit the WHERE clause to see a list of all schemas and who owns them. Replace Kim with the user you’re interested in.

--what schemas does this user own?
SELECT SCHEMA_NAME, 
    SCHEMA_OWNER
FROM INFORMATION_SCHEMA.schemata
WHERE SCHEMA_OWNER = 'Kim';

The results are below.

Fixing the Msg 15138 The Database Principal Owns a Schema error

Ok, we know the schema that’s preventing us from dropping the database. Now we need to fix the problem. To do that, we need to transfer the ownership of the schema to another user. As before there are a couple of ways to do this.

Changing the schema owner using the Properties window

To change the owner of a schema using SQL Server Management Studio or Azure Data Studio, use the connection list to drill down into the server, database, Security, Schemas. Right-click on the schema in question and select the Properties menu item.

On the General page, you’ll see the Schema Owner. Click Search to open a window that allows you to find another user. Enter the username you’d like to be the new owner, and click Check Names. Once, verified, click Ok, and then Ok again to close the Properties window.

Changing who owns a schema using T-SQL

Once again, we can use a quick T-SQL statement instead of the point-and-click method. Run the following statement to change the owner of the schema. Of course, change the schema and username to fit your needs.

--transfer schema ownership to dbo
ALTER AUTHORIZATION ON SCHEMA::db_ddladmin TO dbo;

Who should own a schema?

This begs a question: who should own a schema?

Generally, unless you have a compelling reason to do otherwise, having the schema owner be dbo is often preferred. This keeps things simple and straightforward. A good case for this would be when schemas are used to create a logical workspace or namespace for the database objects.

But a compelling reason may exist. For example, schemas are sometimes used as part of a broader security implementation. You can assign permissions to a schemas and all database objects in the schema will inherit those permissions. That can be convenient. If that’s the case in your environment, it’s worth discussing whether a specific user should own the schema.

So, as is frequently the case, the answer is: It depends.

Want to work with The SERO Group?

Here are some other posts posts that may be helpful, And be sure to check out our Script Library.

• Who’s the SQL Server Database Owner and How Can You Change It?
• What Takes Precedent db_datareader (GRANT) or db_denydatareader (DENY)?
• Who Has sysadmin Access to your SQL Servers?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation. 

Schedule a call with us to get started.