The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

Today I added a new user in my existing database for some web application experiments and when I tried to delete the user I received following error message.

 Complete Error Message:

 Drop failed for User 'NT AUTHORITY\NETWORK SERVICE'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).1004021540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+User&LinkId=20476

ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=15138&LinkId=20476



Reason

If you remember when adding the user 'NT AUTHORITY\NETWORK SERVICE' you marked this new user as database owner.


Now before deleting the user 'NT AUTHORITY\NETWORK SERVICE', you need to mark any other user like 'dbo' as database owner.


And then try deleting the user 'NT AUTHORITY\NETWORK SERVICE'.

If you want to do the same using script, there is a very-very nice tutorial by Pinal Dave sir on the same here 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/

Hope you like it. Thanks.

Comments

  1. Rajiv JainThursday, February 26, 2015 11:26:00 pm

    Waow!! Great you have explained step by step nicely. Very small and common thing, but really very important.

    ReplyDelete

Post a Comment

Popular posts from this blog

Migrating database from ASP.NET Identity to ASP.NET Core Identity

Image
As you know ASP.NET Core Identity (table structure) is different from what we had earlier in ASP.NET Identity. Actually the identity system which we have today with .NET Core is very mature and continuously evolved be it ASP.NET Membership, ASP.NET Identity 1, ASP.NET Identity 2 and now ASP.NET Core Identity. Recently I had to migrate few application to ASP.NET Core and similar its identity database. Because the table schema is changed, i had to re-think and create migration script which I would like to share with you today. It is very simple and easy, just three step and I had everything ready: STEP 1 : Change name of existing tables STEP 2 : Create ASP.NET Core Identity tables STEP 3 : Migrate data from old tables (ASP.NET Identity) to new tables (ASP.NET Core Identity) Script:  https://gist.github.com/itorian/c699e8534b392a6c726ec66c48100072 You should also watch my video, where I demoed migration.
Read More »

Customize User's Profile in ASP.NET Identity System

Image
Note: You should read this post instead, I found below walkthrough will not work on ASP.NET Identity 2. I will re-work on this post soon. In this post you will learn how to customize User’s Profile and add some more fields like FirstName, LastName, EmailID etc with ASP.NET Identity System. In my last post I listed some of the great new features introduced with Identity system .
Read More »