Computer name mismatch when moving database from one PC to another
Posted by Stephen Barnard on 12 October 2020 02:48 PM
Problem : When transferring a database from an old PC to a new one, a user is denied access to the database, even though they are in a P3 user group.
Cause : The database contains the permissions for the P3 user groups and this can contain a specific reference to the PC name. If the old PC name and the new PC name are not the same, then the user group created on the new PC will be different and not have access.
How to confirm : When logged in as the username that installed SQL Server, open SQL management studio. Press connect as required to get to the main dialog.
On the left, expand Databases, then right click on the ProtoCOL3 database name (usually "ProtoCOL3") and select properties.
The "Users or Roles" field should now show 4 items : p2_user, PCNAME\ProtoCOL Admins, PCNAME\ProtoCOL Advanced Users, PCNAME\ProtoCOL Users
Where I have typed PCNAME, it will instead be the name of the PC the database was created on.
Check this against the new PC name.
Open the "System" pane in Windows (keyboard shortcut is Windows Key + "pause/break") by opening the start menu and typing System, then clicking on "system information". The current PC is listed as "System Name" in the right hand side of the default system Information Window.
If these two PC names are different, then the SQL permissions embedded in the database will need to be changed.
Download and unzip this file :
Right click on the script file and select "edit. Select Notepad, if asked what to edit it with.
Inside the file there are 9 references to "OLDMACHINENAME". Replace OLDMACHINENAME with the new PC name, as found in the system pane, above.
Start SQL management studio.
Press connect as required to get into the main dialog.
On the left hand side, expand "Databases".
Select the name of the P3 database, usually "ProtoCOL3"
In the menu bar across the top, click "new query", then copy the entire text of the script file from notepad and paste it into the central pane of management studio.
Click the "parse" button, which looks like a "tick " icon.
It should say something like "parsed successfully"
Then click one the "! Execute" button and it should again say something like "executed successfully"
There is no need to save the query.
Restart SQL management studio and check the database properties, permissions to ensure that where it previously had the old PC name, it now has the new PC name.
The user should now have access to the Database (assuming they are in a user group) and P3 should open normally.