SQL with Manoj

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.

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

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