[This is just for sharing information for those common asked questions collected from forums. If you have any better way or feedback, please directly reply in this thread so that community users can benefit from your answers.]
Question:
I need to move our database completely to a new server. The associated logins, passwords, users and permissions are also required to be moved together.
Answer:
This is a very common asked question, but there is indeed no easy way for a fully automatic process. Though SSIS provides some methods like Transfer SQL Server Login Task, Transfer SQL Server Database Task and Transfer SQL Server Database Object Task, when
you perform a real transfer, you will find that transferring logins, users and user permissions is always a head-ache due to the security design.
Many people usually need to manually reset the login passwords, recreate logins and assign database roles to users after they create a SSIS package with those aforementioned tasks for transfer. Here are the steps:
1.In SSIS, drag Transfer SQL Server Login Task to the Control Flow. Double click on it to open the property editor, set the connections, choose the logins you want to transfer and set the options “IfObjectExists” and “CopySids” per your
favorite.
Note: You can choose "IfObjectExists" to Skip if there are already duplicated logins existing on the target server. In this case, choosing Overwrite may cause errors during execution.
Setting CopySids to True is helpful for transferring Windows domain user accounts when the two servers are in the same domain. If they are in different domains, you need to re-create the logins later.
2.Drag a Transfer Database Task to the Control Flow panel. Double click it to open the property editor, choose the Method of Source Database to “DatabaseOnline”, and set the connections and destination database.
3.Link the two Tasks together from Transfer Login Task to Transfer Database Task.
4.Save and Execute the SSIS package.
5.After that, connect to the target server, manually change the login password for those transferred SQL logins since for SQL logins, after the transfer, their passwords have been reset with random values.
6.As step 1 mentioned, if the Windows login account is in different domain, you need to drop and recreate the logins.
7.Manually assign database roles for users.
To automate the whole process within a SSIS package, you need to consider integrating the steps 5 to 7 to your SSIS package. Before you transfer your database, you can first script out the logins/passwords, login permissions to a logins.sql script file and
user role permissions to another users.sql file, then you can implement an automatic transfer with one Execute SQL Task (executing the logins.sql script file) for creating logins/passwords on the target server, one Transfer Database Task for transferring database
objects to the target server as the step 2, and one Execute SQL Task (executing the users.sql script file) to assign users’ permissions.
More information:
Transfer Login Task
http://technet.microsoft.com/en-us/library/ms137870.aspx
Transfer Database Task
http://technet.microsoft.com/en-us/library/ms141204.aspx
How to move databases between computers that are running SQL Server
http://support.microsoft.com/kb/314546
How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008
http://support.microsoft.com/kb/918992/
Please remember to mark the replies as answers if they help and unmark them if they provide no help
