A SQL Server Login Script Like No Other
You can copy SQL Server logins from one box to another the hard way, or take the easy route by getting an assist from the SP_HELP_REVLOGIN proc.
- By Eric Johnson
- 11/28/2007
Have you ever found yourself with the need to copy SQL Server logins from one server to another? It'll come in handy if you're setting up a failover site, building a replacement server, setting up a reporting instance, or maybe when you just want to backup the logins, just in case. If you are using Windows logins, copying SQL Server logins is a simple matter of scripting the login and applying it to the other server.
Copying SQL Server logins from one box to another, though, is a bit trickier because SQL Server stores and manages the password. So just how do you copy the login and preserve the password? I'm glad you asked.
To successfully copy a login from one server to another, you'll need to ensure that the copy has the same SID and password. The link between database users and logins is done with the logins SID -- if this is different on the new server, then any databases you copy over will contain orphaned users. To ensure that both the SID and the password are the same, Microsoft has written a stored procedure, SP_HELP_REVLOGIN, to aid in our transfer.
SP_HELP_REVLOGIN will return a complete list of the logins that exists on your SQL Server in a script that can be run to recreate them. This script does not exist on your SQL Server by default. You must create with the code provided in Microsoft Knowledge Base Article 918992, "How to transfer the logins and the passwords between instances of SQL Server 2005."
Once you've created the procedures, you can easily generate the create statements that allow you to copy your logins.
As an example, I created a new login on my local instance of SQL Server called SQLScript with a password of scriptme. Now I can run SP_HELP_REVLOGIN as follows:
sp_help_revlogin 'SQLScript'
RESULTS:
/* sp_help_revlogin script
** Generated Oct 30 2007 9:23AM on laptop1 */
-- Login: SQLScript
CREATE LOGIN [SQLScript] WITH PASSWORD = 0x0100B642C5A8BC6778ECE4710ED3DC8D70E0EA31B6DF6B122756 HASHED, SID = 0x80525EB475F8414FB32D627BB876F213, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
Now I have the syntax I need to recreate the login on another box. The SID will be forced to the same value and the passwords will match by virtue of this statement providing the hashed version of the password.
SQL Server Help —
Just An E-Mail Away |
Need SQL Server troubleshooting help? Or maybe you want a better
explanation than provided in the manuals? Describe
your dilemma in an e-mail to the MCPmag.com editors
at [email protected];
the best questions get answered in this column and garner
the questioner with a nifty Redmond T-shirt.
When you send your questions, please include your
full first and last name and location with your message. (If you prefer to remain anonymous,
specify this in your message, but submit the requested
information for verification purposes.) |
|
|
If you need to copy all the logins, SQL Server and Windows logins, you can run SP_HELP_REVLOGIN with no parameters.
Next time, we'll look at replication issues.
About the Author
Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology
consulting. He is also the President of the Colorado Springs SQL Server
User Group. He can be contacted at www.consortioservices.com.