ProjectWise Database User requirements
ProjectWise can run on both Oracle and Microsoft SQL Servers, it uses a multi-user connection in which the ProjectWise Server has a connection with the database (through ODBC or SQL Native Client). Besides this, users access ProjectWise through their own authorization in ProjectWise.For the Database, there is only one user which is really important and needs to be setup with according specifications. When you login via the ProjectWise Administrator, a table check is executed and if you have insufficient privileges, you will keep getting prompted with the create tables dialog. You will need sysadmin/dbo privileges for the “SP” to execute or the tables will not be found correctly.
In both cases the database to be used will have to be in the UniCode format.
General information for servers using 64-bit.
There is no real problem if you use a 64-bit server to install your ProjectWise server. However, since our software is 32-bit, the ODBC datasources will have to be created using the 32-bit version of the tools. In case of Oracle, make sure to install the 32-bit Oracle Client for your database according to the specification, including ODBC driver. The 32-bit version of the ODBC manager can be found in c:\windows\syswow64\odbcad32.exe
Connections made to the database will have to go through the Oracle ODBC driver (NOT the Microsoft ODBC for Oracle).The user in Oracle will need the following privileges
— CONNECT role— CREATE PROCEDURE privilege— CREATE SEQUENCE privilege— CREATE TABLE privilege— CREATE VIEW privilege— UNLIMITED TABLESPACE privilege— EXECUTE permission on the DBMS_LOB package
SQL Server installations
Connections made to the database can go through the SQL server Native client or the ODBC client. No real difference there:
Make sure the user we use for ProjectWise is a user who has in SQL-server the Dbowner privilege.
The easiest way to give the PWDB user the best rights without giving them full database rights is to make them the database owner. Simply go to the database, right-click on properties, select Files, and assign the user as the owner:
The results of this are shown in the user’s User Mapping windows.
Thanks for sharing
Here's a SQL script that I use when creating a ProjectWise database user for Oracle. You just need to edit it for your needs - i.e. change "pwdbuser" and "changeme" to match your username and password and possibly the names of the tablespaces:
SET ECHO OFF
SET SERVEROUTPUT ON
SET LINESIZE 120
SET PAGESIZE 100
SET VERIFY OFF
DROP USER PWDBUSER CASCADE;
CREATE USER pwdbuser IDENTIFIED BY "changeme" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";
GRANT CONNECT TO pwdbuser;
GRANT RESOURCE TO pwdbuser;
GRANT CREATE SESSION TO pwdbuser;
GRANT CREATE VIEW TO pwdbuser;
GRANT EXECUTE ON "SYS"."DBMS_LOB" TO pwdbuser;
GRANT UNLIMITED TABLESPACE TO pwdbuser;