Error 15138 Sql Server 2005
Contents |
| Related Tips: More > Security Problem Recently I had an issue while dropping an orphaned user for an obsolete login. I was unable to
Microsoft Sql Server Error 15138
drop the user and it failed with the below error messages. Msg 15421, microsoft sql server 2012 error 15138 Level 16, State 1, Line 1 The database principal owns a database role and cannot be dropped. Msg 15138, Level msg 15138 sql server 16, State 1, Line 1 The database principal owns a schema in the database, and cannot be dropped. Solution In this article I will explain what needs to be done prior to dropping the
Remove User From Schema Sql Server
user if it failed with error message 15421 or error message 15138. As per Microsoft SQL Security you cannot drop a user in one of the below scenarios: Database Principal/User owns a database role Database Principal/User owns a schema To learn more about orphaned users take a look at this tip Understanding and dealing with orphaned users in a SQL Server database. Query to Get Database Roles
The Database Principal Owns A Database Role And Cannot Be Dropped
Owned by a User You can run this script to get a list of database roles owned by a particular user. Just substitute the orphaned user name where I have "Jugal". -- Query to get the user associated Database Role select DBPrincipal_2.name as role, DBPrincipal_1.name as owner from sys.database_principals as DBPrincipal_1 inner join sys.database_principals as DBPrincipal_2 on DBPrincipal_1.principal_id = DBPrincipal_2.owning_principal_id where DBPrincipal_1.name = 'Jugal' Query to Get Database Schemas Owned by a User You can run this script to get a list of schemas owned by a particular user. Just substitute the orphaned user name where I have "Dj". -- Query to get the user associated schema select * from information_schema.schemata where schema_owner = 'Dj' As a next step to fix the issue we will transfer the ownership of the database role or schema to DBO. We can do this using either SSMS or a T-SQL script. Fix Error Msg 15421 Using SSMS to Fix the Error Go to Object Explorer > Connect to the Target Server > Expand the target Database > Expand Security > Expand Roles > Expand Database Roles-> Right Click on the database role that you need to modify. You can see the user na
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 the database principal owns a service in the database and cannot be dropped a question about how to fix the error while trying to remove a login
Sql Server Drop Schema
from a database. The reader was getting the below error: Msg 15138, Level 16, State 1, Line 1The database principal cannot drop schema because it is being referenced 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 https://www.mssqltips.com/sqlservertip/2620/steps-to-drop-an-orphan-sql-server-user-when-it-owns-a-schema-or-role/ 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 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/ 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) 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 butifu
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 http://zarez.net/?p=179 to 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 each Object and each Database SQL Server Views WITH CHECK OPTION See the http://geekswithblogs.net/naijacoder/archive/2007/08/01/114348.aspx 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 sql server 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 remove the user from database which owns a schema 15138 sql server 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;
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 Tha
July 2012 (1) May 2012 (1) April 2012 (2) September 2011 (2) July 2011 (2) May 2011 (2) April 2011 (1) January 2011 (3) August 2010 (2) July 2010 (2) June 2010 (3) May 2010 (1) April 2010 (4) March 2010 (3) December 2009 (1) November 2009 (2) October 2009 (2) September 2009 (1) July 2009 (1) June 2009 (1) May 2009 (1) April 2009 (2) February 2009 (2) January 2009 (4) December 2008 (3) November 2008 (1) October 2008 (1) September 2008 (3) August 2008 (1) July 2008 (2) June 2008 (2) May 2008 (3) April 2008 (3) March 2008 (1) February 2008 (1) January 2008 (4) November 2007 (3) October 2007 (8) September 2007 (7) August 2007 (8) ADO.NET ADO.NET team blog Ajax Ajaxian AutoSuggestMenu API/WebServices API Directory ASP.NET Resources Matt Berseth BizTalk Server BizTalk 2009 Light and Easy Webcast Series Blogs I read About Fortunate African@Microsoft Alpesh Nakars Andrew Connell (MOSS & WSS) Brandt Fuchs Cornelius Daniel Pollard Dare Obasanjo Darren Gosbell Data Form WebPart David Hayden(Blog) David Hayden(WebSite) EPM Source eworldui.net GAYAN PEIRIS'S(sharepoint) Get DotNet Code GetThePoint Good ECM blog Hezser - Shapoint Ian Morrish(Sharepoint) Ishai Sagi (Sharepoint) Jan Tielen (.Net ,Sharepoint) John Holliday - Sharepoint Reflections joris poelmans (SOA) Karim Erradi Kingsley Idehen Lee Richardson Madhur Ahuja - Sharepoint Michael Freidgeim's Blog (.Net) Microsoft SharePoint Designer Team Blog mitch deeny Mr Office 365 Muhanad Omar Oleg Tkachenko's (XML/XSL) Oracle And .Net Path To SharePoint Patrick Tisseghem's Blog [MVP SharePoint] Patrick Tisseghem's Blog [MVP SharePoint] Paul Andrew poshcode- Powershell Projects Distributor rachel appel Rich Finn Scott Guthrie(ASP.NET) Scott Mitchell (ASP.NET) Sean Cleaver SharePoint 2007, Frode Sørhøy SharePoint Fabian SharePoint Tools sharepointdragons Sql and Xml Ted Pattison's blog That Indigo Girl The blog of Wictor Wilén SP 2013 thekid.me.uk Tips DotNet Top Sharepoint sites Video on Demand Visual Studio 2010 Waldek Mastykarz (Sharepoint) Web BorG Windows 8: Build