Ask your technical questions on forums or here :
ASP.NET or MVC | C# | Windows Phone
Microsoft Technology Journals by Abhimanyu K Vatsa
HOME ABOUT RAZOR BOOK SPEAKING MVC ASP.NET JQUERY VIDEOS EBOOK ARCHIVE

11 Dec 2012

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.

Comment using Google Services (2 comments):

  1. Nice post Abhi Sir, I liked it. Thank u.. :)

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

    ReplyDelete