Minimum required privileges for SQL backend database

To extract data from PeopleSoft using the Process Configuration Insight, you require a user who will be assigned Connect and Select privileges on the SQL backend database.

To create a PSFTHRMS_P2PCI user who will have these privileges, proceed as:

  1. Log into the SQL Server Master database with appropriate user name and password.
  2. Run the following query to check whether PSFTHRMS_P2PCI user already exists in the SQL Server backend database:
    select * from dbo.sysusers where name ='PSFTHRMS_P2PCI';
    • If the APPROVA_PSFTHRMS_P2PCI user is already present and being used by another application, select a different user name and change this script to reflect that user name.
    • If the APPROVA_PSFTHRMS_P2PCI user is not present, run the CreateUser.sql script from the SQL Query Analyzer.
  3. To modify the default user name, password, and database name, change the following parameters in the CreateUser.sql script: 
    set @loginame = PSFTHRMS_P2PCI' /*Login Name*/  
set @passwd = 'approva' /*Login Password*/  
set @defdb = N'HRSDEV’/*Database Name*/
  4. After the CreateUser.sql script is run, execute the following command to go to that backend database: 
    USE EP /*Replace EP with actual database name if it is different*/		  
GO
  5. Execute the GrantDB.sql to grant backend database access. Change the log in name if it is different. 
    s sp_grantdbaccess @loginame = N'PSFTHRMS_P2PCI', /*Login Name */
@name_in_db = N'PSFTHRMS_P2PCI' /*Login Name */
  6. Execute the TableAccess.sql to grant access to the list of tables. Change the log in name if it is different:
    set @loginame = N'PSFTHRMS_P2PCI'/*Login Name */