How do I migrate SQL Logins to a new database server?
This document lists several different ways to migrate SQL logins to a new database server.
Resolving the problem
A common issue associated with migrating SQL Servers that is often overlooked is migrating the SQL Logins to the new SQL Server. SQL Server basically has two levels of user access. The first level is called a SQL Login and the second level is called a Database User. The SQL Login is used to access a specific SQL Server instance and the credentials are stored in the master database. A Database User gets access to a single database on a SQL Server instance and the Database User credentials are stored in that database. Typically, SQL Logins are linked to Database Users so you can use a single username and password to get to a database on SQL instance (this is how TEM Console Operators work).
So, simply transferring the BFEnterprise database to a new SQL Server instance is going to have problems when TEM Consoles try to login. The problem is caused because only the Database Users were copied over with the database and there is no associated SQL Login for them. The BESReporting database does not have any associates SQL Logins; so only the BFEnterprise database can have this problem.
Here are several ways to solve the SQL Login transfer problem for migrating the BFEnterprise database:
- When migrating SQL Server, copy the master database from the old SQL Server to the new SQL Server along with the BFEnterprise and BESReporting databases. The SQL Logins will migrate successfully to the new SQL Server and maintain their links to the TEM Database.
- Run a script to copy the SQL Logins from the old SQL Server to the new SQL Server. A database administrator will typically have such a script available or you can find one online. Click here for an example procedure from Microsoft called sp_help_revlogin.
- Launch TEMAdmin on the new SQL Server and then delete and re-create your TEM Console Operators. If the Console Operator name is not changed your management rights assignments will be preserved.
Further information on performing this operation is available here: http://support.microsoft.com/kb/246133/