Error Msg 15138 The Database Principal Owns a Schema in the Database, and Cannot be Dropped

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.