Resolving Login Errors with Duplicated Databases
Many ISVs and ISV customers often have a need to duplicate their database server in entirety onto another computer system. Such duplicated copies are required often required for development and test purposes and need to be identical to the original database server in all respects.
Copying the database itself is easy and can be done using one of many methods – copying the data and log files to the new server, backing up the database from the original server and restoring it on the new server, etc. However, even after you complete copying the database to the new server the original set of users may not be able to log in to the database on the new server even though the database was duplicated correctly. Users trying to log in may receive the following error message:
Login failed for user ‘TestUser’. (Microsoft SQL Server, Error: 18456)
This error is because even though the database itself was copied over correctly the logins and passwords associated with the database on the original database server instance were not transferred. This occurs because SQL Server logins are not contained within user databases, instead they are held in the syslogins table in the master database.
The login error can easily be remedied by creating the missing logins in the new database. However, this is not always a trivial task because the logins on the original server may have been created manually over a period of time with specific passwords. Changing any of the login’s properties or the associated passwords could have a ripple effect and cause the stress testing or other applications scripts to fail.
To get around this problem you can use the sp_help_revlogin stored procedure on the original server to generate a T-SQL script that creates all the logins existing in the original server. This login script creates the logins that have the original Security Identifier (SID) and the original passwords. This script can then be copied over and executed on the new database server to duplicate the logins and passwords on the new server. The code for the sp_help_revlogin and sp_hexadecimal (which is required by the sp_help_revlogin) stored procedures along with the steps required migrate the logins have been detailed in the Microsoft Knowledge Base article No. 918992 “How to transfer the logins and the passwords between instances of SQL Server 2005” https://support.microsoft.com/kb/918992
and have not been listed in this BLOG to avoid lengthy duplications.
Trying to log in to the new server after you’ve migrated the logins and passwords will result in a successful login.