Copying logins and passwords from one SQL Server to another

Category : Blogs Published : February 18, 2013 User Rating : 5 Stars      Views : 4.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




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 :
exec sp_help_revlogin
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.
Link back to this article : http://www.sqlmatters.com/Articles/Copying logins and passwords from one SQL Server to another.aspx

Keywords

SQL,scripts,login,security


Comments
Post by Will on Wed 28 Oct 2015 06:24. Report Inappropriate Post

Very useful article, it was exactly what i'd needed. Many thanks!
Post by Pazzik on Thu 14 Jan 2016 12:41. Report Inappropriate Post

Perfect !, thank you.
Post by Johnny S on Thu 29 Sep 2016 17:09. Report Inappropriate Post

Nice article. It does appear that the passwords for SQL User logons did not come over with the script. Went from SQL 2008 to SQL 2014. Any reason for that or did I miss something?

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered