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.
The stored procedure script and instructions are in the following Microsoft knowledge base article :
This covers SQL Server versions from 2005 up to 2012, and there is an equivalent article for earlier versions :
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
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 :
exec sp_help_revlogin 'Fred'
This creates the following result, which can be run on the new server to create the login Fred with the same password :
/* sp_help_revlogin script
** Generated Feb 15 2013 11:58AM on SQL2008R2 */
-- Login: Fred
CREATE LOGIN [Fred] WITH PASSWORD = 0x0100B9B5BB9675F9E9507A602F78F03E750590C8A113EFB0EC1D HASHED, SID = 0xC73FD9FAB70D094EB887FFF6B823E4FC,
DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
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.