Solution:

Changes made within Microsoft SQL Server 2000 SP4 and SQL Server 2005, prevents Sage Pro stored procedures from adding existing users to a new company. The solution will discuss how to correct the issue.

PTR 85158 documents this issue and will be addressed in Sage Pro 7.5. To resolve the issue for Sage Pro 7.4, follow the steps below to allow Sage Pro stored procedures to copy all users into the new company database after its creation.

1. Login as an ADMN level user.
2. Open System Manager (SM).
3. Under the Maintain menu, click Dictionary, then Stored Procedures.
4. Select SP10 from the Stored Procedure ID picklist.
5. Click the Stored Procedure tab.
6. Click Edit.
7. Locate the following line in the stored procedure: set @sp_String = ‘select * from master.dbo.syslogins a, ‘+ @db_name + ‘..sysusers b ‘ +                    ‘where a.name = ”’ + @userid  + ”’ and a.sid = b.sid’
8. Modify this line of the procedure to:  set @sp_String = ‘select a.sid from master.dbo.syslogins a, ‘+ @db_name + ‘..sysusers b ‘ +                    ‘where a.name = ”’ + @userid  + ”’ and a.sid = b.sid’
9. Under the Transaction menu, click System Recovery, then Rebuild Stored Procedure.
10. Rebuild stored procedures for all Sage Pro databases.
11. Update the stored procedures in the selected databases from the data dictionary.

This will recompile the stored procedure for all companies and allow existing users to be added to new companies.

The process of copying users to the new company database takes place during the creation of the new company. If a new company is already created without the updated stored procedure, it will require a DBA to add the users. There are two methods to do this:

Method 1

Manually add each user to the new database.

Method 2

1. Logon to SQL Server Query Analyzer as an SA user (or any user with sysadmin roles.)
2. Select your Sage Pro system database (PROSYS).
3. Type the following procedure: exec pro_add_existing_users [new company database name]

© Copyright Sage Software