Transferring logins and passwords between SQL Server instances can be a complex task, but the sp_help_revlogin stored procedure streamlines this process, ensuring a smooth migration. Here’s a comprehensive guide on how to use sp_help_revlogin to transfer logins efficiently.
Why Use sp_help_revlogin?
When moving databases from one server environment to another—such as from instance ABC on server P to instance XYZ on server Q—you also need to transfer the existing logins to the new server to maintain access to the databases.
While manually scripting and transferring a few logins is feasible, this method becomes impractical for a large number of logins. Additionally, SQL Server authentication logins present a challenge, as the passwords are managed by SQL Server, not Windows, leading to potential errors when attempting to log in using the transferred accounts.
The Role of sp_help_revlogin
sp_help_revlogin simplifies this process by generating scripts that replicate logins, including their original passwords, on the new server. This stored procedure eliminates the need to know the original passwords, ensuring a seamless login transfer.
Steps to Use sp_help_revlogin
- Log in to Server P
- Open SQL Server Management Studio (SSMS) and connect to the existing instance (e.g., ABC).
- Create the Stored Procedures
- Open a new query window and run the provided script from the Microsoft documentation. This script creates two stored procedures in the master database: sp_hexadecimal and sp_help_revlogin.
- Generate the Login Scripts
- Execute the following query in a new query window:sqlCopy codeEXEC sp_help_revlogin
- This command generates output scripts containing the logins, original passwords, and Security Identifiers (SIDs).
- Log in to Server Q
- Open SSMS and connect to the new instance where the databases have been restored (e.g., XYZ).
- Transfer the Logins
- Open a new query window on instance XYZ, copy the output script generated in step 3, and execute it. This script creates the logins with their original passwords and correct SIDs.
Understanding the Stored Procedures
- sp_help_revlogin: Generates the code to recreate logins with their original SIDs.
- sp_hexadecimal: Converts the passwords into a text format using a password hashing algorithm for inclusion in the script.
Conclusion
By utilizing the sp_help_revlogin stored procedure, you can efficiently transfer logins with their original passwords to a new SQL Server instance. This method saves time and ensures the integrity of your login credentials during the migration process, while also providing a valuable learning experience in the functionality of stored procedures.