Ms Sql Server Error 15138
Contents |
SERVER - Fix: Error: 15138 - The database principal owns a schema in the database, and cannot be dropped
Remove User From Schema Sql Server
December 26, 2011Pinal DaveSQL, SQL Server, SQL Tips the database principal owns a database role and cannot be dropped and Tricks94 commentsLast day I had excellent fun asking puzzle on SQL Server the database principal owns a service in the database and cannot be dropped Login SQL SERVER – Merry Christmas and Happy Holidays – Database Properties – Number of Users. One of the user sent me email
Sql Server Drop Schema
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 cannot
The Database Principal Owns A Fulltext Catalog In The Database And Cannot Be Dropped
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;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('pinaladmin'
MichelDecember 19, 20101 Share 0 0 Description of the problem: When you tried to drop a user, you gotthis cannot drop schema because it is being referenced message: Error: 15138 The database principal owns a schema in the
Alter Authorization On Schema
database, and cannot be dropped. Cause: That means, you are trying to drop a user owning drop failed for user a schema. In 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. 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/ Resolution: You can 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 https://blogs.technet.microsoft.com/mdegre/2010/12/19/the-database-principal-owns-a-schema-in-the-database-and-cannot-be-dropped/ dbo GODROP USER myUser 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) Priva
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 http://zarez.net/?p=179 Server How to Script SQL Server Configuration Options How to List all Disabled http://stackoverflow.com/questions/35434389/how-to-drop-a-sql-server-user-with-db-owner-privilege 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 Default Schema of SQL Server Database Users and Groups How to List all SQL Logins with Passwords not Enforced sql server 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 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 the database principal 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: 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 d
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up How to drop a SQL Server user with db owner privilege up vote 0 down vote favorite I need to drop a user with dbowner schema from a SQL Server database. I cannot drop it as it is since I get this error message Drop failed for User 'network service'. (Microsoft.SqlServer.Smo) The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138) When I try to uncheck the schema owned by this user to remove db owner it does nothing. My question is how I can drop this user or edit its name from 'network service' to 'NT AUTHORITY\NETWORK SERVICE' sql-server sql-manager share|improve this question edited Feb 16 at 14:32 marc_s 454k938701033 asked Feb 16 at 13:50 Sofia Khwaja 323112 Possible duplicate of How to delete user with db_owner rights? –Sankar Raj Feb 16 at 13:55 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote accepted take a look at this: http://www.itorian.com/2012/12/the-database-principal-owns-schema-in.html It suggests that you need to add another owner first share|improve this answer answered Feb 16 at 14:07 Kevin M 948 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post as a guest Name Email discard By posting your answer, you agree to the privacy policy and terms of service. Not the answer you're looking for? Browse other questions tagged sql-server sql-manager or ask your own question. asked 8 months ago viewed 215 times active 8 months ago Linked 2 How to delete user with db_owner rights? Related 1675Add a column, with a default value, to an existing table in SQL Server884How to return the date part only from a SQL Server datetime datatype1155How to check if a column exists in SQL Server table2077UPDATE from SELECT using SQL Server305How to drop a table if it exists in SQL Server?1Wonderin