Obtaining P6 EPPM PXRPTUSER Object Creation Scripts For Primavera Data Service (PDS) Customers (Doc ID 2889437.1)

PRE-REQUISITE NOTES

NOTE #1:

• This instruction assumes you've already created P6 EPPM (configCode=ds_p6adminuser) schema tables in your localized database from a PDS extraction.

NOTE #2:

• This instruction assumes you've already created a reporting user within your database.
• Execute the following if you have not yet created this schema user in your database:
  • FOR AN ORACLE DATABASE

create user pxrptuser
identified by pxrptuser --MODIFY TO A USER DEFINED PASSWORD
temporary tablespace temp --MODIFY TO YOUR TEMP TABLESPACE NAME
default tablespace pmdb_px_dat1 --MODIFY TO YOUR DATA TABLESPACE NAME
/
grant create session, create view, create synonym to pxrptuser
/

• • FOR A SQL SERVER DATABASE

create login [pxrptuser] WITH PASSWORD = 'pxrptuser', CHECK_POLICY = OFF --MODIFY TO A USER DEFINED PASSWORD
go
exec sp_grantdbaccess N'pxrptuser',N'pxrptuser'
go
grant create table, create synonym, create view to pxrptuser
go

NOTE #3:

• This instruction assumes you've granted select permission to the reporting user for the P6 EPPM schema tables
• Execute the following if you have not yet granted this permission to the user:
  • FOR AN ORACLE DATABASE

DECLARE
v_p6user VARCHAR(30) := 'ADMUSER'; --MODIFY TO YOUR ACTUAL ADMUSER NAME
v_reportinguser VARCHAR(30) := 'PXRPTUSER'; --MODIFY TO YOUR PXRPTUSER NAME
BEGIN
FOR c_tbl IN (SELECT table_name FROM all_tables WHERE owner = v_p6user) LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || v_p6user || '.' || c_tbl.table_name || ' TO ' || v_reportinguser;
END LOOP;
END;
/

• • FOR A SQL SERVER DATABASE

DECLARE @v_reportinguser varchar(30)
DECLARE @v_grantstmts TABLE(stmt varchar(500))
DECLARE @v_sqlstmt varchar(500)
SET @v_reportinguser = 'PXRPTUSER' --REPLACE WITH ACTUAL PXRPTUSER NAME
INSERT INTO @v_grantstmts SELECT 'GRANT SELECT ON ' + NAME + ' TO '+ @v_reportinguser FROM sysobjects WHERE TYPE = 'U'
DECLARE c_tbl CURSOR FOR SELECT stmt FROM @v_grantstmts
OPEN c_tbl
FETCH NEXT FROM c_tbl INTO @v_sqlstmt
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @v_sqlstmt
EXECUTE (@v_sqlstmt)
FETCH NEXT FROM c_tbl INTO @v_sqlstmt
END
CLOSE c_tbl
DEALLOCATE c_tbl