Minimum required privileges for SQL backend database

To extract data from the Authorizations Insight for PeopleSoft HRMS, a user requires Connect and Select privileges on the SQL backend database

To create an approva_hr user who will have these privileges:

  1. Log into SQL Server Master database with appropriate user name and password.
  2. Run the following query to check whether approva_hr user already exists in SQL Server backend database:
    Select * from dbo.sysusers where name ='approva_hr';
    • If the approva_hr 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_hr user is not present, run the CreateUser.sql script from the SQL Query Analyzer.
    Note: Minimum permission scripts are shipped and can be obtained from the IRC Adapters CD under PeopleSoft\MinimumPermissions folder.
  3. To modify the default user name, password and database name, make the changes to the following parameters in the CreateUser.sql script: 
     set @loginame= N'approva_hr'/*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 the 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. 
    sp_grantdbaccess @loginame = N'approva_hr', /*Login Name */ 
@name_in_db = N'approva_hr' /*Login Name */
  6. Execute TableAccess.sql to grant access to the list of tables. Change log in name, if it is different. 
    set @loginame = N'approva_hr'/*Login Name */