Error 15138 Sql Server 2008 R2
Contents |
SERVER - Fix: Error: 15138 - The database principal owns a schema in the database, and cannot be dropped December 26, 2011Pinal DaveSQL, SQL Server, SQL Tips and Tricks93 commentsLast day I error 26 in sql server 2008 r2 had excellent fun asking puzzle on SQL Server Login SQL SERVER – error 18456 in sql server 2008 r2 Merry Christmas and Happy Holidays – Database Properties – Number of Users. One of the user sent me error 40 sql server 2008 r2 email asking urgent question about how to resolve following error. Reader was trying to remove the login from database but every single time he was getting error and
Sql Server 2008 R2 Download
was not able to remove the user.The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)As per him it was very urgent and he was not able to solve the same. I totally understand his situation and here is the quick workaround to the issue. The reason for error is sql server 2008 r2 management studio quite clear from the error message as there were schema associated with the user and that needs to be transferred to another user.Workaround / Resolution / Fix:Let us assume that user was trying to delete user which is named as ‘pinaladmin' and it exists in the database ‘AdventureWorks'. Now run following script with the context of the database where user belongs.USE AdventureWorks;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('pinaladmin');
In my query I get following two schema as a result.Now let us run following query where I will take my schema and and alter authorization on schema. In our case we have two schema so we will execute it two times.ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_denydatawriter TO dbo;Now if you drop the database owner it will not throw any error.Here is generic script for res
in SQL Server OUTPUT Clause in SQL Server SQL Server Configuration Manager not found in Programs sql server 2008 r2 end of life list Set First Day of the Week in SQL Server
Sql Server 2008 R2 Requirements
Built-in Logical Function CHOOSE in SQL Server How to Script SQL Server Configuration Options How to
Sql Server 2008 R2 Pricing
List all Disabled Indexes in a SQL Server Database How to Disable and Enable Index in SQL Server SQL Server Buffer Pool Memory used by http://blog.sqlauthority.com/2011/12/26/sql-server-fix-error-15138-the-database-principal-owns-a-schema-in-the-database-and-cannot-be-dropped/ each Object and each Database SQL Server Views WITH CHECK OPTION See the Default Schema of SQL Server Database Users and Groups How to List all SQL Logins with Passwords not Enforced by Policies How to List all Locked SQL Logins in SQL Server EOMONTH Function to Get First and Last Day http://zarez.net/?p=179 of a Month in SQL Server Concatenate SQL Server Columns that contain NULL values How to convert VARCHAR to Integer in SQL Server with CAST and CONVERT « « » » How to enable and disable trace flags, or check for active trace flags in SQL Server How to drop database user that owns a schema - SQL Server Error: 15138 When you try to remove the user from database which owns a schema in the database you will get the following error: The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138) and you will not be able to remove the user until you change the schema owner. To change the schema owner from Sql Server Management Studio: Expand your database -> Security -> Schemas. In the Object Explorer Details you can see a list of the schemas and the owners: