Error 15138 Sql Server 2008
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 and Tricks93 the database principal owns a schema in the database and cannot be dropped. sql server 2008 commentsLast day I had excellent fun asking puzzle on SQL
Remove User From Schema Sql Server
Server Login SQL SERVER – Merry Christmas and Happy Holidays – Database Properties – Number of Users.
The Database Principal Owns A Database Role And Cannot Be Dropped
One of the user sent me email asking urgent question about how to resolve following error. Reader was trying to remove the login from database but every
The Database Principal Owns A Service In The Database And Cannot Be Dropped
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 understand his situation and here is sql server drop schema 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');
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 TO dbo;
ALTER AUTHORIZATION ON 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
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 question about how 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/ to fix the error while trying to remove a login from a database. The http://sqlquickfixes.blogspot.com/2012/12/drop-failed-for-user-in-sql-server.html 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 schema before removing the User. In this post sql server 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 the above query: Then, use the names found from the above the database principal 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 world & her sacrifices nd prayers r d cornerstone of my success #OneIndianGirl 1monthago Follow @subhroleo1MenuHome About myself.. My Articles.. Subhro Saha's Public Pr
getting the same error again and again, where we feel we cant anything more than that. When ever trying to delete a user from the database, you will get to see this error below. The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138). The error itself is the meaning. It can't be dropped as it owns a schema. Solution : -- Simple.. :) Go to the security under the Database where you wanted to delete the user from, goto users --> Right Click on the username--> select Properties Now you can see clearly in General Tab Schemas Owned by this user: So please note the selected 'schemas' here, It doesn't matter if it is greay/inactive. Now exit out from that window. (OR) Simple Query : SELECT *, 'UserName' as ownerName FROM sys.schemas s WHERE s.principal_id = USER_ID('UserName') Now you have to do the following : Go to Schamas under security, select the schamaName which you have noted down above or from the query result.Right Click on it and select the properties. Now you can see the that same username placed as Schema Owner in General Tab Replace that username with dboandclick OK button. Do the same for all the schema's you have noted above. Now you can be able to delete the user. You have otherprocessesto do this, This is just one of them. Please give me your feed back. New ideas most welcome. Posted by PraveenSynick at 1:39 PM Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest No comments: Post a Comment Newer Post Older Post Home Subscribe to: Post Comments (Atom) Blog Archive ► 2015 (12) ► June (1) ► May (1) ► April (5) ► March (2) ► February (1) ► January (2) ► 2014 (10) ► December (4) ► November (2) ► October (1) ► May (1) ► April (1) ► February (1) ► 2013 (12) ► December (1) ► November (5) ► April (5) ► January (1) ▼ 2012 (14) ▼ December (3) Drop Failed for User in SQL server SmartScreen Filter Latest Backup and Restoration Dates on Databases ► November (5) ► October (6) About Me PraveenSynick View my complete profile Watermark template. Powered by Blogger.