Month: June 2021

I am not able to drop a SQL user and keep getting this error message;

Msg 15138, Level 16, State 1, Line 5

The database principal owns a schema in the database, and cannot be dropped.

There is an orphan user who owns a schema or role and can not be dropped until user is detached from schema/role.

First see if there is any role associated and remove it;

-- Query to get the orphan users
EXEC sys.sp_change_users_login 'REPORT'

-- Query to get the user associated Database Role
SELECT 
	DBPrincipal_2.name as [Role], DBPrincipal_1.name as [OWNER] 
FROM sys.database_principals as DBPrincipal_1 
INNER JOIN sys.database_principals as DBPrincipal_2 
	ON DBPrincipal_1.principal_id = DBPrincipal_2.owning_principal_id 
WHERE DBPrincipal_1.name = 'ADDUSER'

--Query to fix the role
ALTER AUTHORIZATION ON ROLE::[db_owner] TO [dbo]

SSMS STEPS: Object Explorer->Target Server->Target Database->Security->roles->Right click on database role. Change user name to your selected name or “dbo” and click OK.

Now fix the issue where we will transfer the ownership of the database role/schema to dbo.

----*** Query to get the user associated schema
select * from information_schema.schemata
where schema_owner = MyUser'

--Query to fix the error Msg 15138 on database schema
ALTER AUTHORIZATION ON SCHEMA::[MyDatabaseSchema] TO [dbo]

--Query to drop the user
DROP USER [MyUser]
GO

SSMS STEPS: Object Explorer->Target Server->Target Database -> Security->Schemas->Right Click on schema->Change user name to your selected name or “dbo”.

Schema and/or database role has been transferred to “dbo”. You are safe to drop the user.