Microsoft Sql Server 2005 Error 15138
Contents |
MichelDecember 19, 20101 Share 0 0 Description of the problem: When you tried to drop a user, you gotthis message: microsoft sql server error 15138 Error: 15138 The database principal owns a schema in the database, remove user from schema sql server and cannot be dropped. Cause: That means, you are trying to drop a user owning a schema. In the database principal owns a database role and cannot be dropped order to drop the user, you have to find the schema that’s assigned and then transfer the ownership to another user/role or to drop it. Resolution: You can the database principal owns a service in the database and cannot be dropped fix the issue following two ways. By script: You can find out which schema is owned by this user with the query below: SELECT name FROM sys.schemasWHERE principal_id = USER_ID(‘myUser')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 myUser
Sql Server Drop Schema
By Management Studio: - Object Explorer >>Expand the [databasename]>> Security. - Click on Schemas. - In summary window, determine which Schema(s) are owned by the user and either change the owner or remove the Scheme(s).- If they are system schema(s), I suggest to change them to ‘dbo’.- Drop your user. More detail aboutschemas into the BOL:http://msdn2.microsoft.com/en-us/library/ms190387.aspx Michel Degremont| Premier Field Engineer -SQL Server Core Engineer | Tags Security Comments (1) Cancel reply Name * Email * Website Kevin says: June 2, 2016 at 11:59 am Brilliant, many thanks, life saver! Reply Follow UsPopular TagsSQL Server SSAS Cluster Installation Security SQL Server High Availability Windlows Cluster AMO Powershell DBA TIP Analysis Services Kerberos Replication T-SQL Management Studio DC High Availability Migration SSIS Service Packs Archives November 2013(1) September 2013(1) May 2013(1) April 2013(1) March 2013(3) December 2012(1) October 2012(1) September 2012(1) August 2012(1) June 2012(3) All of 2013(7) All of 2012(30) All of 2011(44) All of 2010(36) All of 2009(23) Privacy & Cookies Terms of Use Trademarks © 2016 Microsoft
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,
The Database Principal Owns A Fulltext Catalog In The Database And Cannot Be Dropped
sys.schemas Today one of my reader asked a question about how to cannot drop schema because it is being referenced fix the error while trying to remove a login from a database. The reader was getting the alter authorization on schema 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 https://blogs.technet.microsoft.com/mdegre/2010/12/19/the-database-principal-owns-a-schema-in-the-database-and-cannot-be-dropped/ explanatory as there were schema associated with the user and we have to transfer those 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 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/ 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 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
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 Configuration Options How to List all Disabled Indexes in a SQL http://zarez.net/?p=179 Server Database How to Disable and Enable Index in SQL Server SQL Server Buffer Pool Memory used http://www.dotnetnukeblog.de/post/Microsoft-SQL-Server-Error-15138-Drop-User-Account.aspx 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 EOMONTH Function to Get First and Last Day of a Month in SQL Server Concatenate SQL Server Columns that contain NULL values sql server 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 the database principal 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;
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 schema - SQL Server Error: 15138 burana March 15, 2014 at 1:54 am · Reply it work! Many Thanks, IrrerIvan May 9, 2014 at 8:54 am · Reply Hey man, Thanks for this hint!! It works fine! Greetings from Germany Robby Salomon September 26, 2014 at 2:29 pm · Reply Thanks man! This works All the best, Robby tryden March 20, 2015 at 7:30 pm
den Mircosoft SQL Express 2008 eingespielt. Dabei wollte ich ein wenig aufräumen und die nicht benötigten Benutzerkonten löschen. Jedoch habe ich bei dem Versuch immer die Fehlermeldung"The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)." bekommen. Die Lösung für dieses Problem ist recht einfach: Wenn für den Benutzeraccount ein Schema angelegt wurde, muss zunächst das Schema aus der SQL Server Datenbank entfernt werden. Um zu überprüfen, ob es ein Schema für den Benutzer gibt, kann man mit dem SQL Server Manager bei der entsprechenden Datenbank unter Sicherheit => Schema nachschauen. Wenn kein Schema mehr von diesem Benutzer existiert, lässt sich auch das Benutzerkonto ohne Fehlermeldung löschen. Datum: Juni 22, 2011 22:16 Tags: Kategorien: SQL Server Submit to DotNetKicks... 0Kommentare Ähnliche BeiträgeSQL 2005 Server error: Property IsLocked is not available for Login '[username]' Heute musste ich feststellen das auf einem Windows 2003 Server mit einer Instance vomMS SQL Se...Microsoft SQL Server 2005 für verschiedene KundensegmenteDie neue Version des MS SQL Servers wird für verschiedene Anforderungen der Kunden verfügbar sein. M...XML in SQL Server 2005Microsoft bietet in den nächsten Tagen mal wieder eine Reihe von Webcast zum Thema MS SQL Server 200... Kommentar schreiben Name* E-Mail* Webseite Kommentar* Benachrichtige mich bei neuen Kommentaren Antwort auf Kommentar abbrechen 5+5 = The captcha value you provided is incorrect. Wichtige Seiten...DatenschutzhinweiseExample pageImpressumWer bin ich Search Tag cloudArchiv 2013 Februar (2) 2012 Dezember (1)November (2)Juli (1) 2011 November (2)Oktober (2)August (1)Juli (1)Juni (1)März (3)Januar (5) 2010 Dezember (1)Oktober (3)September (4)Juli (1)Juni (1)April (4)März (2)Februar (2)Januar (1) 2009 November (1)Oktober (1)September (2)August (1)Juli (4)Juni (2)Mai (4)April (2)März (2)Februar (1)Januar (2) 2008 November (2)Oktober (5)September (1)August (3)Juli (3)Juni (2)Mai (3)April (3)März (1)Februar (4)Januar (4) 2007 Dezember (3)November (1)September (6)August (2)Juli (5)Mai (4)April (5)Februar (2)Januar (5) 2006 Dezember (6)November (7)Oktober (5)September (5)August (3)Juli (4)Juni (5)Mai (4)April (4)Februar (4)Januar (7) 2005 Dezember (6)November (9)Oktober (5)September (4)August (8)Juli (13)Juni (10)Mai (14)April (9)März (16)Februar (21) Category list.NET (1