SymptomsWhen attempting to delete an MSSQL 2005 database using Plesk, the following error message is displayed:
Error: Unable to delete the database user: Unable to remove database user: Delete database user failed: The database principal owns a database role and cannot be dropped.
This often happens with databases of the DotNetNuke application.
CauseThe database user owns a role or a schema.
Resolution1. For each database user that cannot be deleted, its role or schema ownership needs to be identified. To do this, the following query can be used:
select dp2.name as role, dp1.name as owner from sys.database_principals as dp1 inner join sys.database_principals as dp2 on dp1.principal_id = dp2.owning_principal_id where dp1.name = 'Username'
(where Username is the name of the user that needs to be deleted).
2. The owner of this principle (role) should be changed to dbo. This can be done using the role manager in SQL Management Studio.
3. Once these changes have been made, it will be possible to delete the database and the user.