Thursday, April 21, 2016

How to transfer logins and passwords between instances of SQL Server

This article describes how to transfer the logins and the passwords between instances of Microsoft SQL Server 2005, of Microsoft SQL Server 2008, and of Microsoft SQL Server 2012 on different servers.

The script in this article creates two stored procedures in the master database: sp_hexadecimal and sp_help_revlogin.

https://support.microsoft.com/en-us/kb/918992


Having the SIDs of sql logins match across multiple instances is especially useful in various scenarios including:

1.  Where the production database is frequently restored onto development and test instances, no more needing to run sp_change_users_login after every refresh

2. Mirrored and High Availability Group databases so that sql logins on the secondary instance match the users in the database after a failover.

No comments:

Post a Comment