Home » SQL Server Error : 15138, Severity: 16. The database principal owns a %S_MSG in

SQL Server Error : 15138, Severity: 16. The database principal owns a %S_MSG in

sql server DBA 999

SQL Server Error : 15138 Details


SQL Server Error: 15138
Severity: 16
Event Logged or not: No
Description:
The database principal owns a %S_MSG in the database, and cannot be dropped.
Severity 16 Description:
Indicates general errors that can be corrected by the user.

Drop failed for User ‘VBR’.
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error:15138)

Cause for SQL Server Error 15138

Because the error clearly states that this user has a schema in the database, we should investigate the schema that this user owns. To check the owned schema for a user in the database, please follow the steps below.

In SSMS, connect to a SQL Server instance.

Expand the database folder, then the database name where your user is found or created.

Within your target database, expand the Security folder, then the Users folder, and double-click the target user you wish to remove.

A section titled “Owned Schemas” can be found. This section lists the schemas that this user owns.

If any schema owned by this user is discovered in the preceding steps….

Reading sql server error log location from SQL Query

Identifying SQL Server Error Log File used by SQL Server Database Engine can be done by reading SQL Server Error Logs. DBA can execute the XP_READERRORLOG extended stored procedure to read the SQL Server Error Log and search for its location used by the instance of SQL Server.

USE master
Go
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'
Go

For other ways to read and find error log location please our article https://sqlserver-dba.co.uk/error-log/sql-server-identify-location-of-the-sql-server-error-log-file.html

Solution for Resolving the Error

The schema which is owned by this user is identified in above steps in cause of error. Now next step is to change is owner of the identified schema and then drop the login.

–Change DBName with your database name and Schema_Name with your schema name which is owned by this user.
USE [DatabaseName]
G0
Alter Authorization ON Schema::[SCHEMA_NAME] TO [dbo]
Go

Once above command will execute, next step is to drop the user again. Run below command to drop the user.

In below script change DBName with your database name where user exists and USERNAME with user name which you want to drop.
USE [DatabaseName]
Go
DROP USER USERNAME
Go

This time it will work and user will drop from the database. You can also delete this user from SSMS by right click on the user and choose the delete option. SSMS way is given below:

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

SQL Server Error Code and solution summary


SQL Server Error: 15138
Severity: 16
Event Logged or not: No
Description:
The database principal owns a %S_MSG in the database, and cannot be dropped.

Leave a Reply

Your email address will not be published. Required fields are marked *