We recently went through an exercise to move most of our SQL Server databases onto another server. Moving the databases was easy enough, we just backed them up and restored them into the new environment. However that left the problem of how to move the logins. We could have right clicked on each login in SQL Server Management Studio and scripted them up using the 'Script Login as' menu item. However whilst that would create the logins and set up the default database etc, it would not include the password (the script just creates a random password for security reasons). Of course if your server only has Windows Authentication logins this blog won’t be of relevance because all of the passwords will be maintained by Windows rather than SQL Server, it will only be of use if you have SQL Server logins.

If we knew the password for each login we could have just pasted this into the script, but unfortunately for us for many of the logins the password was not going to be easy to obtain (OK, we should have kept a record of all passwords but for a variety of reasons this didn't always happen).

Fortunately help is at hand with a useful 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 (hashed) format, so while the login will be have the same password on the target server, you can’t use this information for other purposes. For example it won’t give you the password if you want to enter it into a user application that connects to the database via the login.

Stored Procedure

The stored procedure script and instructions are in the following Microsoft knowledge base article :

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

This covers SQL Server versions from 2005 up to 2012, and there is an equivalent article for earlier versions :

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

The instructions in the KB article are quite comprehensive, it's just a case of running the script, which installs two stored procedures into the master database sp_help_revlogin and sp_hexadecimal. To script out all logins just run the following statement in SQL Server Management Studio :
If you want to script just one login then just specify the login as a parameter. For instance to script out the login 'Fred' just run :
This creates the following result, which can be run on the new server to create the login Fred with the same password :
There are a few 'gotchas' which are mentioned in the Microsoft article, particularly when moving logins between different versions of SQL Server. However one useful feature is that the SID of the login will be the same as on the source server. This means that there shouldn't be any orphaned logins to fix.