Ms Sql 2005 Error 15138
Contents |
SERVER - Fix: Error: 15138 - The database principal owns a schema in the database, and cannot be microsoft sql server error 15138 dropped December 26, 2011Pinal DaveSQL, SQL Server, SQL Tips
Remove User From Schema Sql Server
and Tricks94 commentsLast day I had excellent fun asking puzzle on SQL Server
The Database Principal Owns A Database Role And Cannot Be Dropped
Login SQL SERVER – Merry Christmas and Happy Holidays – Database Properties – Number of Users. One of the user sent me email
The Database Principal Owns A Service In The Database And Cannot Be Dropped
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 was not able to remove the user.The database principal owns a schema in the database, and sql server drop schema 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 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; MichelDecember 19, 20101 Share 0 0 Description of the problem: When you tried to drop a user, you gotthis message: the database principal owns a fulltext catalog in the database and cannot be dropped Error: 15138 The database principal owns a schema in the database, cannot drop schema because it is being referenced and cannot be dropped. Cause: That means, you are trying to drop a user owning a schema. In alter authorization on schema 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 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/ 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 https://blogs.technet.microsoft.com/mdegre/2010/12/19/the-database-principal-owns-a-schema-in-the-database-and-cannot-be-dropped/ 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 den Mircosoft SQL Express 2008 eingespielt. Dabei wollte ich ein wenig aufräumen und die nicht benötigten Benutzerkonten löschen. Jedoch habe http://www.dotnetnukeblog.de/post/Microsoft-SQL-Server-Error-15138-Drop-User-Account.aspx 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 http://www.bigresource.com/MS_SQL-Drop-User-with-Msg-15138--k73xvDAl.html 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 sql server 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... the database principal 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 database, and cannot be dropped. Then I tried: select * from information_schema.schemata where schema_owner = 'TestUser' CATALOG_NAME SCHEMA_NAME SCHEMA_OWNER ---------------- ------------- ------------ DB_Installation db_datareader TestUser (1 row(s) affected) Drop schema TestUser Msg 15151, Level 16, State 1, Line 1 Cannot drop the schema 'TestUser', because it does not exist or you do not have permission. any idea? thanks -D View 5 Replies Similar Messages: Drop All Indexes In A Table, How To Drop All For User Tables In Database Cannot Drop User. Can Anyone Help? Drop User In Mssql Unable To Drop User SQL 2005 Cannot Drop User Can Not Drop User From Database Drop Role, User, Login SSRS Drop Down - Allow User To Select Value To Appear In A Report SQL 2012 :: Unable To Drop Database User Drop And Add A Sql Server Login Account To Each User Database How To Drop A User Defined Database Role In 2005? Granting A User Permissions To Create And Drop A Table SQL 2012 :: User Impact To Drop A Noncluster Index On Table While In Use? SQL Server 2012 :: Cursor Function And Drop User / Logon Howto Setup My User In Order To Grant Him The Drop Privilege SQL Server Express 2005 - How To Delete/drop A User Instance Failed To Generate A User Instance Of SQL Server Due To Failure In Retrieving The User's Local Application Data Path. Please Make Sure The User Has A Local User Profile On The Computer. The Connection Will Be Closed How Do We Determine Which User Database Tables Are Mostly Retrieved By User Or Modified By User? Rollback Will Drop Created Tables And Drop Created Tables In Transaction..? Generating User Instances In Sql Server Is Disabled. Use Sp_configure User Instances Enabled To Generate User Instances. Error : Cannot Open User Default Database. Login Failed. Login Failed For User 'server/user' Cannot Open User Default Database. Login Failed For User 'NT AUTHORITYNETWORK SERVICE' SQL Server 2012 :: Query To Find User Who Last Modified User Roles / Access? [JavaScript Error] Cannot Delete User Or User's Authority In Specific Report After Install SQL SP2 Second User Trying To Connect Generates: Cannot Open User Default Database. Login Failed. Login Failed For User 'testuser'. The User Is Not Associated With A Trusted SQL Server Connection. Login Failed For User 'NT AUTHORITYANONYMOUS LOGON' (but User.identity.name Is Correct) Error Login Failed For User ''. The User Is Not Associated With A Trusted SQL Server Connection. Login Failed For User 'test'. The User Is Not Associated With A Trusted SQL Server Connection. Login
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('pinaladmin'