Home > SQL Errors > SQL Error – The database principal owns a schema in the database, and cannot be dropped

SQL Error – The database principal owns a schema in the database, and cannot be dropped

October 10, 2015 Leave a comment Go to comments

Have you ever faced this error while dropping a USER from a Database? Today while dropping a USER I faced this error and for a while I was not able to fix it. After checking here & there and searching online I found some clues and got the reason for this error.

User_Drop01

–> Let’s check the reason behind it and how to fix it:

USE [AdventureWorks2014]
GO

DROP USER [hruser]
GO

You get following error:

Msg 15138, Level 16, State 1, Line 5
The database principal owns a schema in the database, and cannot be dropped.

The above error explains very well why the USER cannot be deleted. It’s because the USER is associated with a SCHEMA and has its ownership. Thus until and unless you assign another USER as the SCHEMA owner you cannot DROP this USER.
 

–> To check which SCHEMA this USER owns you can use either of these 2 methods:

Method #1: By simple Query:

SELECT *
FROM sys.schemas
WHERE principal_id = USER_ID ('hruser')
GO
name	schema_id	principal_id
Sales	9		5

 

Method #2: By GUI in SSMS: Right click on the user name under Database -> Security -> Users

User_Drop02

–> So, all you need to do is change the ownership of the Schema from the USER that you want to delete to some other user like dbo, as done below:.

USE [AdventureWorks2014]
GO

ALTER AUTHORIZATION ON SCHEMA::[Sales] TO [dbo]
GO

If there are more SCHEMAs then issue ALTER AUTHORIZATION ON SCHEMA statement on those schemas also to change their owners.
 

–> Ok, now just re-run the DROP USER statement, it will drop the User.

USE [AdventureWorks2014]
GO

DROP USER [hruser]
GO

Advertisement
  1. Prakhar agarwal
    October 10, 2015 at 3:19 pm

    Nice Explanation Sir…

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: