How to fix ORPHANED SQL Server users

Symptons/Issue

After moving or restoring the RASWINSQL SQL Server database to a different server machine, you cannot access the database from the RASWIN application program. Assuming you have properly adjusted the RASWIN ODBC SETTINGS for the connection to the new database, the most likely problem is that is that the Quadrant user REG001 in the RASWINSQL database has been made an "ORPHAN".

This means that the the user id associated with the user on the new server is not linked to the same user id in the Quadrant database.

This is true even if there is a user id REG001 that appears to be present, in both the RASWINSQL or SQL Server master database. When a new user id is created, SQL Server assigns a GUID to it and it must match between the server master and RASWINSQL databases.

Typically, when a database is moved (or 'restored') to a new server, the existing user id 's are carried over within the security settings of the database, but are not automatically added in the proper manner to the new server MASTER database . If you just create a new USER ID on the new server, the GUID assigned when the user is created will not match the one assigned by the old server to the one in the database for that user.

Solution

There is a stored procedure that does the hard work of correcting this issue with just a few commands.

All of these instructions should be done as the database admin, with the restored database selected (e.g., RASWINSQL). First, make sure that this is the problem. Using the SQL Query Analyzer, make sure you have selected the RASWINSQL database.

USE RASWINSQL

This command will list the orphaned users:

EXEC sp_change_users_login 'Report'

For the Quadrant RASWIN program the only user ID required is REG001 with a password of REG001.

If you see others listed they may or may not require adjusting depending on what you may be using them for. In older versions of RASWIN, a separate user id was established for each workstation (e.g., REG001, REG002, REG003, etc.) RASWIN was modified to use only a single user id, REG001 regardless of register number.

Your database may contain these older users (REG002 and higher) but they can safely be ignored. Often, due to database schemas that have been created or assigned using the other user id's, it is just easier to leave them in place.

If you have set up other user ID's for external processes such as queries or reporting, that is fine and they may need to be 'adopted' as described below if they don't allow those other processes to connect.

Additional ODBC connections may be required for interfaces to external systems such as utility billing, permitting, parking, building permits, business license, etc.

If REG001 shows up in the list, it is an ORPHAN USER. If you already have a login id and password for this user, ADOPT IT by executing this command:

EXEC sp_change_users_login 'Auto_Fix', 'REG001'

If you want to create a new login id and password for this user, CREATE IT by executing this command:

EXEC sp_change_users_login 'Auto_Fix', 'REG001', 'REG001', 'REG001'

Comments

Note: The information above discusses the Quadrant RASWINSQL database, but can be used to correct similar issues with other databases as well if the issue with access is related to the same cause.