Genesys Hacking - Calling List Filter SQL

Product: Genesys Suite
Version: 5.x and above (worked with 8)
Module: Outbound Contact Server

Follow query can display list of filters available for all campaigns in OCM. It is useful when developing custom campaign or calling list import program, which uses filter defined in OCM


connect gowner@cfg
select fltr.Name, fltr.Description, Flex.Prop_Name, flex.Prop_Value
from cfg_filter fltr, cfg_flex_prop flex
where flex.object_type = 29
and flex.prop_type = 0
and flex.parent_dbid <> 0
and flex.object_dbid = fltr.dbid
and fltr.tenant_dbid = 101

In MS SQL Server, and MySQL database server, it is possible to configure the database, table, or column to be case insensitive. Following example show how to make the SELECT to be case sensitive for MS SQL Server. There are several other strategy to make the global, or default for column which I won't cover here. Ask me if you need assistant


select fltr.name, prop_value
from cfg_filter fl, cfg_flex_prop flexp
where flexp.object_type = 29 and flexp.prop_type = 0 and flexp.parent_dbid <> 0 and flexp.object_dbid = fl.dbid and fl.tenant_dbid = 101
and fl.name = 'HR' collate sql_latin1_general_cp1_cs_as

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