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

In MS SQL Server database, it is very often to setup database user name wrongly during initial Genesys product installation. We need to create multiple databases, as well as multiple login to each database for owner, viewer, and reporting.

The most frequent mistake is for CCA database, where the installation document does not clearly document database related credential, and expect all Genesys architect is MS SQL Server security expert. In development, or QA environment, Genesys engineer/consultant often tested with full privilege, or simply use sa user, which is a biggest security breach.

Once a test user is created, you may need to undo it, and re-create again to ensure the step is documented, or correct the wrongly created credential.

This step covers database user dropping, which touching following area:

1. Login user (created under MS SQL Server)
2. Database user (created under each database, e.g. CCA)
3. Schema user (created under each database, e.g. CCA)

How to drop database user when encountering following error

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

In this example, I created login name reporter, with DB user reporter_dbo (User name specified in Users Mapping page during login user creation), and set default schema to db_datareader. Sample database used is Genesys CCA database

I will firstly assign DB user db_datareader back to db_datareader, which is build-in DB user. Then I will be able to drop DB user reporter.

Remember to set the database name prior of executing the script, because this is database specific. If DB user reporter assigned to multiple default schema in each database, then execute this SQL in each database.

Step:
use CCA


SELECT cast(s.name as varchar(20)) schema_name, cast(u.name as varchar(20))  user_name FROM sys.schemas s, sys.sysusers u
where s.principal_id = u.uid


schema_name          user_name
-------------------- --------------------
db_accessadmin       db_accessadmin
db_backupoperator    db_backupoperator
db_datawriter        db_datawriter
db_ddladmin          db_ddladmin
db_denydatareader    db_denydatareader
db_denydatawriter    db_denydatawriter
db_owner             db_owner
db_securityadmin     db_securityadmin
dbo                  dbo
guest                guest
INFORMATION_SCHEMA   INFORMATION_SCHEMA
db_datareader        reporter_dbo
sys                  sys

(13 row(s) affected)

1. Navigate to database CCA - Security - Schemas - db_datareader. Double click on db_datareader to change its schema owner

2. Under page "General" click on "Search..." button

3. Click "Browse..." button

4. Select object name "[db_datareader]" so that it assigns back to original system user

5. Click OK button to save the result

6. Execute this SQL (very similar to above SQL) to confirm the change

SELECT cast(s.name as varchar(20)) schema_name, cast(u.name as varchar(20))  user_name FROM sys.schemas s, sys.sysusers u

where s.principal_id = u.uid and s.name = 'db_datareader'

schema_name          user_name

-------------------- --------------------

db_datareader        db_datareader

7. Now drop the DB user reporter_dbo (under CCA database - Security - Users), and login user reporter (under server name - Security). This error won't appear now