In SQL Servers 2005 the concept of a schema has been added to the database. ProjectWise V8i handles the schema as part of the qualifying the object names in the database. In earlier versions of ProjectWise the additional schema layer can be handled as long as the schema name is the same as the user name and more so the login name.
Many times with SQL Server databases the database is left to be owned by the System Admin account or SA login and DBO schema. I typically like to see databases assigned to specific users and their own schema. This isolates the ProjectWise database from other databases on the same instance of SQL Server. By default it allows better management of the database as well as making it easier to secure the database.
If this is the approach you took when setting up your SQL Server 2000 database for ProjectWise you may encounter a problem where the user who owns the database is orphaned in SQL Server 2005. This problem occurs when migrating a database from SQL Server 2000 to SQL Server 2005 or SQL Server 2000 to SQL Server 2008. When a specific user is designated, the database user and schema will be created in the new version of the database in SQL Server 2005. Unfortunately there will not be a login associated with the user.
The following stored procedure can be used to resolve this problem and associate the user with an existing login.
Example:
So in SQL Server 2005 we create a login "pwadmin" with a password "pass1". From the SQL Server 2000 database that we have restored to SQL Server 2005 we have an orphaned user "pwadmin". This user is orphaned because the login pwadmin is not associated with the user pwadmin. To resolve this, the following stored procedure can be used to automatically associate the pwadmin user with the pwadmin login.
EXEC sp_change_users_login 'Auto_Fix', 'pwadmin', NULL, 'pass1'
Here is some additional information on this topic.
sp_change_users_login - Maps an existing user in a database to a Microsoft® SQL ServerTM login.
http://msdn2.microsoft.com/en-us/library/aa259633(SQL.80).aspx
BUG: Using the Auto_Fix Option with sp_change_users_login Can Leave Security Vulnerabilities
http://support.microsoft.com/kb/298758
How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/kb/246133/