Error 15138 Sql Server
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 sql server error 15023 and Tricks93 commentsLast day I had excellent fun asking puzzle
Sql Server Error 18456
on SQL Server Login SQL SERVER – Merry Christmas and Happy Holidays – Database Properties – sql server error 3729 Number of Users. One of the user sent me email asking urgent question about how to resolve following error. Reader was trying to remove the login sql server 2008 error 15138 from database but every single time he was getting error and 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
Remove User From Schema Sql Server
understand his situation and here is the quick workaround to the issue. The reason for error is 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; in SQL Server OUTPUT Clause in SQL Server SQL Server Configuration Manager not found in Programs list Set First Day of the Week in SQL Server Built-in Logical Function CHOOSE in SQL Server How to Script SQL Server the database principal owns a database role and cannot be dropped Configuration Options How to List all Disabled Indexes in a SQL Server Database How to the database principal owns a service in the database and cannot be dropped Disable and Enable Index in SQL Server SQL Server Buffer Pool Memory used by 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 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/ EOMONTH Function to Get First and Last Day 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 http://zarez.net/?p=179 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: Right click on the schema that is owned by the user you want to delete and change the owner (Properties -> General -> Schema Owner). Query: Run the following query if the user you are trying to drop is named for example ‘my_app_user’ and it exists in the database ‘AW2008’: USE AW2008; cannot be dropped!! 05 Wednesday Feb 2014 Posted by Subhro Saha in SQL Server: Administration ≈ 1 Comment TagsAlter Authorization, Database Principal, Drop User, Schema, SQL Error 15138, sys.schemas Today one of my reader asked a https://subhrosaha.wordpress.com/2014/02/05/sql-server-error-15138-the-database-principal-owns-a-schema-in-the-database-and-schema-cannot-be-dropped/ question about how to fix the error while trying to remove a login from a database. The reader was getting the below error: Msg 15138, Level 16, State 1, Line 1The database principal owns a schema in the database, and cannot be dropped. The error message of SQL Server is self explanatory as there were schema associated with the user and we have to transfer those sql server schema before removing the User. In this post I will explain the workaround for this error: Lets assume I am trying to drop a user named "TestUser" from DemoDB database. Now, run the below query in the database from which we are trying to drop the user. Use DemoDB ;SELECT s.nameFROM sys.schemas sWHERE s.principal_id = USER_ID(‘TestUser') In my test scenario, I got the below result set from sql server error the above query: Then, use the names found from the above query below in place of the SchemaName below. And drop your user. ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo GODROP USER TestUser In my Test scenario I used the below queries: ALTER AUTHORIZATION ON SCHEMA::db_securityadmin TO dbo;ALTER AUTHORIZATION ON SCHEMA::db_ddladmin TO dbo; GO DROP USER TestUser Hope this helps. Cheers, Subhro Saha Subhro's BlogGoogle Related Post navigation ← Previous post Next post → 1 thought on “SQL Server: Error: 15138-The database principal owns a schema in the database, and schema cannot be dropped!!” Pingback: How To Fix Error 15138 Sql Server Errors - Windows Vista, Windows 7 & 8 Leave a Reply Cancel reply Enter your comment here... Fill in your details below or click an icon to log in: Email (Address never made public) Name Website You are commenting using your WordPress.com account. (LogOut/Change) You are commenting using your Twitter account. (LogOut/Change) You are commenting using your Facebook account. (LogOut/Change) You are commenting using your Google+ account. (LogOut/Change) Cancel Connecting to %s Notify me of new comments via email. Notify me of new posts via email. Search: Subhro Saha Subhro SahaTwitter My fav indian girl is my Mother.Bcoz f her I m in dis butiful wo
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 Sql Server Drop Schema
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('my_app_user'); Here is my result: And change the owner of the schema to some other user (for example dbo): ALTER AUTHORIZATION ON SCHEMA::db_datawriter TO dbo; When the user you want to delete has no schemas owned you can delete it. If the post helped you, please share it: May 25th, 2012 | Tags: SQL Server | Category: SQL Server 9 comments to How to drop database user that owns a