|
||
Migrating SQL Server logins can be a difficult task to pull off correctly if you’re not sure about your options or haven’t done it before.Obvious reasons for migrating logins include cases when you’re transferring SQL Servers to newer or bigger hardware. But you’ll also need to migrate logins if you’re using something like Log Shipping or Database Mirroring to provide a High-Availability solution in order to ensure that your applications and users can keep accessing data in the event of an emergency or failover. SQL Server 2000 provided a Transfer Logins DTS task and SQL Server 2005 and 2008 provide a similar Transfer Logins task for SQL Server Integration services. However, it’s been my sad experience that both of these tasks just don’t always work out in many cases. Which can leave you hanging if you’re trying to migrate logins—especially to different sites or via VPNs. Happily though, one of the easiest ways that I’ve found to migrate logins is just to persist them to a file or script. To take this approach, you’ll need to follow the instructions outlined by one of two different KB articles published by Microsoft. The first, KB246133, shows how to transfer logins between servers running SQL Server 7 or 2000 as well as how to transfer logins from SQL Server 7 and 2000 up to SQL Server 2005 instances. The second, KB918992, shows how to do the same thing - but between servers running SQL Server 2005 (and 2008) only. Other than accounting for syntactical changes between SQL Server versions, the approaches defined by both KB articles is roughly the same though in that they guide you through the creation of two new stored procedures. The first, sp_hexadecimal, enables SQL Server to write-out SQL Server login passwords—so that they can be persisted to flat files. The second sproc, sp_help_revlogin, iterates through each login defined on the local server, and then outputs the necessary scripts needed to recreate that login. And, in the case of SQL Server logins - where SQL Server knows the password, the passwords are also written out as part of the login generation script in hexadecimal. For Trusted or Windows Logins, all SQL Server is obviously able to do is generate the CREATE LOGIN script necessary to grant the Windows user or principal in question permissions to log into SQL Server. The problem though, is that you’ll need to ensure that these principals exist on your target server before you run the generated script - otherwise SQL Server obviously won’t be able to trust a Windows account for login if it doesn’t exist. By using the stored procedures provided by Microsoft, you can quickly generate a script to recreate all logins on your source server. You can then parse that script, remove any logins you don’t want to migrate, and then ensure that all Windows accounts that you’ll want to trust on the destination sever have been created or defined. Then, you can run the generated script on your destination server, and your logins will have been successfully migrated. In closing, just be aware that if you already had copies of your migrated databases on a destination server before copying the logins, then you may end up with orphaned logins—where mappings between logins and users get lost. Happily, troubleshooting this problem isn’t too hard, and Microsoft has provided KB274188 to help guide you through this process. Of course, if you’d rather see a step-by-step tutorial of how to use these scripts rather than slugging through the KB articles mentioned, then you can watch a free video showing you how to Copy and Move SQL Server Logins at www.SQLServerVideos.com/video/copying-and-moving-sql-server-logins/ HOW CAN IDERA HELP? Idera’s SQL admin toolset includes a Login Copy tool that compares and synchronizes logins between servers, saving you time and minimizing the risk of errors. Try SQLadmin toolset for free! Download a free 14-day trial: www.idera.com/Products/SQL-Server/SQL-admin-toolset/ ABOUT THE AUTHOR Michael K. Campbell (mike@sqlservervideos.com) is a consultant with years of SQL Server DBA and development experience. He spends most of his time engaged in consulting, technical evangelism, and creating free online SQL Server Videos. Watch the free Copy and Move SQL Server Logins video: Download a free 14-day trial: www.sqlservervideos.com/video/copying-and-moving-sql-server-logins/ For additional information or to download a 14-day evaluation of any Idera product, please visit: www.idera.com. |
||