Every DBA runs into the following topic from time to time in their career, How Do you go about Migrating Logins from One SQL Server to Another? From time to time you'll want to move actual sql login users to another server be it an upgrade, or if you have a good development - test - production cycle, you might be more used to this. Or maybe just maybe you're starting this cycle out, initially just run the Sproc provided by Microsoft called sp_help_revlogin There are two methods posted on the Microsoft site:
You will need to use Query Analyzer for Sql Server 2000 or Management Studio for 2005/2008
|Copy the results and paste them to the new query window|
- Go to the source server and open up a new query window and be sure you are pointing to your source database
- check that your Management studio is set to return your results as text
- you can do this by right clicking within the query window then choosing results to text
- Paste your script into the query window and execute the script by clicking the run button or F5.
- copy the results and open up a new Management Studio Query window that is connected to your Destination Server and be sure you've selected the destination database.
- Paste the result text, and edit out any users you don't want to re-create
These steps are only necessary if you need to migrate users from one physical server to another. You won't need to run these steps if you are on the same server and you've simply created your test database in the same environment, the users already exist in the database from your backup and restore, and they already exist in the server system database as well. This is only necessary when you have two completely separate servers/instances.
Good luck and happy TSQLing