KB6858: How to create MicroStrategy user accounts from an existing customer repository in MicroStrategy Administrator - Command Manager

 
This document is a sample methodology on how to synchronize existing MS SQL Server logins to create new Strategy user accounts in the Strategy Metadata, via Strategy Administrator - Command Manager. It is imperative that this workaround be first tested upon a development environment, so that any possible issues can be determined and resolved before implementing upon the production system.
 
Sample Testing Environment Setup:
This test was performed using sample data and a Microsoft SQL Server database. For the purpose of the test, four tables were created. The Data Definition Language for each table is described below.

1. USER_TABLE: The purpose of this table is to store the current users that are allowed to connect to the Strategy project. As specified by the requirements sheet, this table contains three columns and a unique index on the recip_id column.
   /*DDL for creating USER_TABLE */
   drop table USER_TABLE
   
   create table USER_TABLE (
   recip_id integer,
   recip_name varchar(255),
   recipient_type tinyint
   )
   create unique INDEX recip_id_idx1
   on USER_TABLE(recip_id)
   
2. STAGING_USERS: This table is loaded by an external system and contains the users that need to be present in the Strategy Project after the batch window completes. The purpose of storing these users in the staging table and not in the user_table is because this table will later be used to produce the list of users to be created and list of users to be dropped.
   /*DDL for creating STAGING_USERS*/
   drop table STAGING_USERS
   
   create table STAGING_USERS (
   recip_id integer,
   recip_name varchar(255),
   recipient_type tinyint
   )
   create unique INDEX recip_id_idx2
   on STAGING_USERS(recip_id)
   
3. STAGING_CREATE_USERS: The purpose of this table is to store the users that will ultimately be created in the Strategy repository. As later will be explained in the store procedure definition, it stores the users that do not exist in the system (USER_TABLE) and that do exist in the STAGING USERS entity.
   /*DDL for creating STAGING_CREATE_USERS*/
   drop table STAGING_CREATE_USERS
   
   create table STAGING_CREATE_USERS (
   recip_id integer,
   recip_name varchar(255),
   recipient_type tinyint
   )
   create unique INDEX recip_id_idx
   on STAGING_CREATE_USERS(recip_id)
   
4. STAGING_DROP_USER_TABLE: The purpose of this table is to store the users that will ultimately be deleted from the Strategy 7 repository. This table will store the users that are present in the USER_TABLE and do not exist in the STAGING_USERS table.
   /*DDL for creating STAGING_DROP_USER_TABLE*/
   drop table STAGING_DROP_USER_TABLE
   
   create table STAGING_DROP_USER_TABLE (
   recip_id integer,
   recip_name varchar(255),
   recipient_type tinyint
   )
   
   create unique INDEX recip_id_idx
   on STAGING_DROP_USER_TABLE(recip_id)
   

 
Sample testing data:
The following SQL Statements were executed to create a testing environment.
/* insert some sample data onto this table. These are current users on the system */
insert into USER_TABLE values (1,'Abraham', 1)
insert into USER_TABLE values (2,'Adiletta', 1)
insert into USER_TABLE values (3,'Akhnoukh', 1)
insert into USER_TABLE values (4,'Aldrete', 1)
insert into USER_TABLE values (5,'Ali', 1)
insert into USER_TABLE values (6,'Anderson', 1)
insert into USER_TABLE values (7,'Arora', 2)
insert into USER_TABLE values (8,'Ashbrook-Barnes', 2)
insert into USER_TABLE values (9,'Avila', 2)
insert into USER_TABLE values (10,'Back', 2)
insert into USER_TABLE values (11,'Baskine', 2)
insert into USER_TABLE values (12,'Brenes', 2)
insert into USER_TABLE values (13,'Bruni', 2)
/* insert some sample data onto this table. These are users that need to be in the system after the batch window */
insert into STAGING_USERS values (1,'Abraham', 1)
insert into STAGING_USERS values (2,'Adiletta', 1)
insert into STAGING_USERS values (3,'Akhnoukh', 1)
insert into STAGING_USERS values (4,'Aldrete', 1)
insert into STAGING_USERS values (14,'Bryant-Hall', 1)
insert into STAGING_USERS values (15,'Bui', 1)
insert into STAGING_USERS values (16,'Cackowski-Schnell', 1)
insert into STAGING_USERS values (17,'Camacho', 1)
insert into STAGING_USERS values (18,'Chen', 1)
insert into STAGING_USERS values (19,'Chernow', 1)
insert into STAGING_USERS values (20,'Collins', 1)
insert into STAGING_USERS values (21,'Cooley', 1)
insert into STAGING_USERS values (22,'Deng', 2)
insert into STAGING_USERS values (23,'Dey', 2)
insert into STAGING_USERS values (24,'Freixas', 2)
 
Generation of Stored Procedures that will be called from a (ISQL) from Command Line:

1. sp_update_staging_tables
2. sp_create_user_script
3. sp_create_sec_filter
4. sp_drop_user_script
5. sp_cleanup_user_table

 
These stored procedures are used to update the staging tables and to generate the Create User, Create Security Filter and Delete User Command Manager instructions.
 
1. sp_update_staging_tables:
create procedure sp_update_staging_tables
as
/* BEGIN STORED PROCEDURE*/
/* This SQL statement inserts into the Staging_Drop_User_Table those users that need to be dropped which are those */
/* that exist in the current User_Table that do not exist in the new user table */
insert into STAGING_DROP_USER_TABLE select * from USER_TABLE where recip_id not in (select recip_id from STAGING_USERS)
/* This SQL statement inserts into the Staging_Drop_User_Table those users that need to be dropped which are those */
/* that exist in the current User_Table that do not exist in the new user table */
insert into STAGING_CREATE_USERS select * from STAGING_USERS where recip_id not in (select recip_id from USER_TABLE)
/* END STORED PROCEDURE*/
/*The execution of the stored procedure will need to be scheduled*/
 
2. sp_create_user_script:
create procedure sp_create_user_script
as
/*Begin*/
/*This SQL will generate the Command Manager Script that will be used to create the users*/
set nocount on
select 'CREATE USER "' ++ recip_name ++ '" PASSWORDEXP NEVER;' from STAGING_CREATE_USERS
/*End*/
 
3. sp_create_sec_filter:
create procedure sp_create_sec_filter
as
/*Begin*/
/*This SQL will generate the Command Manager Script that will be used to create security filters*/
set nocount on
select 'CREATE SECURITY FILTER "' ++ 'SecFilter' ++ LTRIM(STR(recip_id)) ++ '"' ++ ' IN PROJECT' ++ ' "MyProjectName" '
++ 'FILTER' ++ ' "MyFilter" ' ++ 'TOP ATTRIBUTE LIST' ++ ' "' ++ LTRIM(STR(recip_id)) ++ '"'
++ ' BOTTOM ATTRIBUTE LIST' ++ ' "' ++ LTRIM(STR(recip_id)) ++ '";'
from STAGING_CREATE_USERS where recipient_type = 1
/*End*/
 
4. sp_drop_user_script:
create procedure sp_drop_user_script
as
/*Begin*/
/*This SQL will generate the Command Manager Script that will be used to drop the users*/
set nocount on
select 'DELETE USER "' ++ recip_name ++ '";' from STAGING_DROP_USER_TABLE
/*End*/
 
5. sp_cleanup_user_table:
create procedure sp_cleanup_user_table
as
/*Begin*/
/*Finally, we recycle the USER_TABLE*/
DELETE FROM USER_TABLE
INSERT INTO USER_TABLE SELECT * FROM STAGING_USERS
/*End*/
 
Execution of stored procedures and Strategy Administrator - Command Manager scripts:
When running these five newly created stored procedures, via ISQL (Command Line utility for SQL Server commands), there will be three Strategy Administrator - Command Manager scripts that will later be executed via command line. All the scripts (ISQL and Strategy Administrator - Command Manager) can be embedded into a single batch (.bat) file that can later be executed using Windows NT Scheduler, Windows AT command, or a SQL Server scheduled task.
/*How to invoque all the commands from Command Line*/
isql -SA_FREIXAS1 -Utester -Ptester -dVMALL -Q"EXEC sp_update_staging_tables" -h-1 -w500
isql -SA_FREIXAS1 -Utester -Ptester -dVMALL -Q"EXEC sp_create_user_script" -o "c:\create.txt" -h-1 -w500
isql -SA_FREIXAS1 -Utester -Ptester -dVMALL -Q"EXEC sp_create_sec_filter" -o "c:\secfilter.txt" -h-1 -w500
isql -SA_FREIXAS1 -Utester -Ptester -dVMALL -Q"EXEC sp_drop_user_script" -o "c:\drop.txt" -h-1 -w500
isql -SA_FREIXAS1 -Utester -Ptester -dVMALL -Q"EXEC sp_cleanup_user_table" -h-1 -w500
del c:\drop.log
del c:\create.log
"C:\Program Files\Strategy\Administrator\Command Manager\cmdmgr.exe" -n "Sample Projects (Direct)" -u Administrator -p Administrator -f "c:\drop.txt" -o "c:\drop.log"
"C:\Program Files\Strategy\Administrator\Command Manager\cmdmgr.exe" -n "Sample Projects (Direct)" -u Administrator -p Administrator -f "c:\create.txt" -o "c:\create.log"
"C:\Program Files\Strategy\Administrator\Command Manager\cmdmgr.exe" -n "Sample Projects (Direct)" -u Administrator -p Administrator -f "c:\secfilter.txt" -o "c:\secfilter.log"
 
WARNING
Manually editing values in the Strategy Metadata incorrectly may cause serious, project-wide problems that may make your project unusable. Since these are user-initiated changes, they are not covered by any Strategy warranty. Users are strongly encouraged to backup Metadata prior to any alteration.