Microsoft sql server error 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.

Problem

You are trying to drop a database user, but are getting the following error message:

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

This error is self-explanatory as it tells you that the database user you are trying to drop is the database schema owner.

Resolution

To successfully drop the database user, you must find all the schemas that are owned by the database user, and then transfer their ownership to another user.

Here is the Transact-SQL script, which I wrote a while ago, to drop the database user. This script first transfer’s ownership of all database schemas associated with particular database user to the specified database user, and then drops that database user from the database.

To use this script, change the following two local variables of this script:

• @SQLUser – Specify the name of the database user that you want to drop
• @NewSchemaOwner – Specify the name of the database user that will be used as new schema owner for the schemas that is owned by the database user, which you are dropping

-- Ensure a USE database_name statement has been executed first.
SET NOCOUNT ON;

DECLARE @ID [int] ,
		@CurrentCommand [nvarchar](MAX) ,
		@ErrorMessage   [nvarchar](2000) ,
		@SQLUser        [sysname] , --Specify the name of the database user that you want to drop
		@NewSchemaOwner [sysname];  --Specify the name of the database user that will be used as new schema
								    --owner for the schemas that is owned by the database user you are dropping

SET @SQLUser = N'Specify_Database_User_You_Want_To_Drop'; --Example: testuser
SET @NewSchemaOwner = N'Specify_Database_User_Who_Will_User_As_New_Schema_Owner'; --Example: liveuser

DECLARE @Work_To_Do TABLE
    (
      [ID] [int] IDENTITY(1, 1)
                 PRIMARY KEY ,
      [TSQL_Text] [varchar](1024) ,
      [Completed] [bit]
    );

INSERT  INTO @Work_To_Do
        ( [TSQL_Text] ,
          [Completed]
        )
        SELECT  N'ALTER AUTHORIZATION ON SCHEMA::' + [name] + SPACE(1) + 'TO'
                + SPACE(1) + QUOTENAME(@NewSchemaOwner) ,
                0
        FROM    [sys].[schemas]
        WHERE   [principal_id] = USER_ID(@SQLUser);

INSERT  INTO @Work_To_Do
        ( [TSQL_Text] ,
          [Completed]
        )
        SELECT  N'DROP USER' + SPACE(1) + @SQLUser ,
                0

SELECT  @ID = MIN([ID])
FROM    @Work_To_Do
WHERE   [Completed] = 0;

WHILE @ID IS NOT NULL
    BEGIN
        SELECT  @CurrentCommand = [TSQL_Text]
        FROM    @Work_To_Do
        WHERE   [ID] = @ID;

        BEGIN TRY
            EXEC [sys].[sp_executesql] @CurrentCommand
            PRINT @CurrentCommand
        END TRY
        BEGIN CATCH

            SET @ErrorMessage = N'"Oops, an error occurred that could not be resolved. For more information, see below:'
                + CHAR(13) + ERROR_MESSAGE() 

            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

            GOTO ChooseNextCommand
        END CATCH

        ChooseNextCommand:

        UPDATE  @Work_To_Do
        SET     [Completed] = 1
        WHERE   [ID] = @ID

        SELECT  @ID = MIN([ID])
        FROM    @Work_To_Do
        WHERE   [Completed] = 0
    END;

SET NOCOUNT OFF;