Datamover: Exporting a user and its securities (Roles, etc.)

Using the following Datamover script, you can extract a single user along with its corresponding roles and securities to a flat file:

SET OUTPUT C:\Refresh\USEREXPORT.DAT;
SET LOG C:\Refresh\USEREXPORT.LOG;

EXPORT PSOPRDEFN WHERE OPRID='userid';
EXPORT PSOPRALIAS WHERE OPRID='userid';
EXPORT PSROLEUSER WHERE ROLEUSER='userid';
EXPORT PSUSERATTR WHERE OPRID='userid';
EXPORT PSUSEREMAIL WHERE OPRID='userid';
EXPORT PSUSERPRSNLOPTN WHERE OPRID='userid';
EXPORT PS_ROLEXLATOPR WHERE ROLEUSER='userid';
EXPORT PS_RTE_CNTL_RUSER WHERE ROLEUSER='userid';


The out put would be in the .DAT file you have specified above and you can use this file as your input when importing the user back to the database.



Importing a single user from Backup flat file:


Note: You cannot use a where clause when importing from a flat file (.DAT)
hence, it is not possible to load a single user from backup directly.

Scenario: Your PeopleSoft environment just had a database refresh. The delivered userexport.dms script was used to backup the original user accounts.

Issue: After the refresh, you noticed that a specific user that existed in the original database is missing from the newly refreshed database.

Solution: You may follow the steps below to extract only the missing user account and its securities. In this example, we will be utilizing the script mentioned above.

1. Backup the user accounts in your current database using the userexport.dms script. Make sure that you do not over-write the pre-refresh user backup.
2. Load the pre-refreshed user backup ny running the userimport.dms script.
3. Run the export script above to export the desired user account only.
4. Import the backup created in step 1 to restore the current users.
5. Run the script below to import the user you've extracted in step 3.

SET INPUT C:\location\of\your\backup\in\step3.DAT;
SET LOG C:\location\of\your\logfile.LOG;
SET  UPDATE_DUPS;

IMPORT *;

PS: This script was used on a PeopleTools 8.46 with MS SQL Database environment.