Transfer logins

Recreate SQL Logins:

Imagine a scenario where you need to uninstall and reinstall
sql server. In such a case you may need to recreate the logins. Assume that
you dont have the password for the sql logins that are to be recreated.
In such a case we have a option of scripting all the logins ( or any )
into a SQL script. SQL Script on execution on the new server will
recreate the logins previously created with the SAME password.
The best thing is the password is not exposed in the script.

This script also works to transfer logins from sql 7 to sql 2000 ,
7 to 2005 and 2000 to 2005.This script and the method is described in detail in Microsoft website.But as this is my blog let me give a brief description of the method.

1) First on the source( old server) create the sps sp_hexadecimal,sp_help_rev_login,
sp_help_revlogin_2000_to_2005( if you are transfering from 2k to 2k5 )
on the master database.

2) On the Query analyzer run

EXEC sp_help_rev_login 'Loginname' --for a single login name
EXEC sp_help_rev_login --for all logins

3) Output will be 'Create Login ...'. Save the output on a .sql file.
Execute the same on the destination server to have the logins created with the same password. Simple. Just a small note.
Have the query analyzer result window in text mode ( ctrl + t) so that
the formating of the output script is not distrubed.

Rest of the details ( and the one repeated here :) ) are given clearly
in microsoft website. Take a look.


For SQL Server 7 and 2000 and for transferring logins from 7 to 2k5 and 2000 to 2005

http://support.microsoft.com/kb/246133

For transferring logins between Sql Server 2005 servers.

http://support.microsoft.com/kb/918992