How do I use SSIS to completely transfer my database including logins, users, and permissions from one server to another server? RRS feed

  • General discussion

  • [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.]

    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.
    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
    Transfer Database Task
    How to move databases between computers that are running SQL Server
    How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008

    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Tuesday, June 22, 2010 1:39 PM

All replies

  • can i use configuration file for transferring database and logins and users and permissions
    Monday, February 7, 2011 3:15 PM