Does anyone know how to fix up orphaned local Windows users in a SQL database?
I'm attempting to backup and restore a database to a different machine. Neither machine is in a domain - they are in different workgroups. Several Windows users have permissions in the database on the source machine and in order to facilitate reconnecting the users in the target machine, I've set up users with the same usernames (but different passwords) there.
Unfortunately this doesn't work. The database users are restored somewhat uselessly not just as [user name] but as [source machine name]\[user name], preventing linking of accounts on the target machine. Does anyone know how it's possible to reconnect those users to the correct windows accounts? I did the usual Googling, but the information available is generally for connecting orphaned SQL users, not for connecting orphaned local Windows users.
A summary of conversations around this subject ...
At our site we "drop" the users and add them back in appropriately. To make this easier on ourselves we have users in a domain group (or groups), and then grant appropriate access priveleges to those groups.
If this (moving databases) is a reasonably common occurrence it's usually best to create a script to do this - a colleague has generated such a script by querying the sysuers table along the lines of
SELECT 'some appropriate text for this user' + name FROM dbo.sysusers where sid is not null.
He then pastes the results of that query into another query window and saves that as the script