Transfer logins and passwords between instances of SQL Server
Recently I am working on a migration projects, I have installed a new database server and backup databases and restored the database into the new server. Now the challenge is how to move the logins?
Of course I can right click on each login in SQL Server Management Studio and script them up using the ‘Script Login as‘ menu item, and use the script to create the logins and set up the default database on the new server but that would not include the password. Unfortunately we have some legacy application login users and not easy to obtain the password.
Note: The passwords will be maintained by Windows or Domain for Windows Authentication logins and not by SQL Server; but we have the mix not only Windows Authentication logins.
Fortunately I found stored procedure from Microsoft which will script up logins so that they can be recreated with the original password. Note that the password is generated in the script in an encrypted format,
While the login will be have the same password on the target server, but it won’t give you the password (password is encrypted in the script)
1- Download and run the script to create two stored procedures in the master database. The procedures are named sp_hexadecimal and sp_help_revlogin. Script
2- Then from SQL Server Management Studio run the statement below to script out all logins.
exec sp_help_revlogin
3- From SQL Server Management Studio run the statement below to script out specific logins
As an example script out ‘Ahmet’ logins
exec sp_help_revlogin ‘Ahmet’
This creates the following result, which can be run on the new server to create the login Ahmet with the same password :
/* sp_help_revlogin script
** Generated Feb 10 2012 11:58AM on SQL2008R2 */
— Login: Ahmet
CREATE LOGIN [Ahmet] WITH PASSWORD = 0x0100B9B5BB9675F9E9507A602F78F03E750590C8A113EFB0EC1D HASHED, SID = 0xC73FD9FAB70D094EB887FFF6B823E4FC,
DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
IT mug 