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. 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

Customize User's Profile in ASP.NET Identity System