How to Handle Logins After Detaching database and Reattaching it To Another SQL Server Instance

  • I have 2 instances of SQL Server on the same machine. I have been directed to detach the databases from the 2005 instance and attach them to the 2008 instance. I have completed this and now need to move the logins over as well. Is there a quick way of doing this? Right now I plan on opening the old login and duplicating it on the 2008 instance.

  • SSIS has a transfer logins task

  • Thanks, worked like a charm. However I need to move the mdb files again. Is there any way to save the the logins with their database permission settings intact when doing a detach/attach? Using the login transfer task worked as far as moving them over but I still had to go in and set permissions for each database because when you detach a database the login loses the setting for the database because the database is no longer there.

  • look into sp_change_users_login 'auto_fix', <loginname>, null after the reattach.

    It'll take the users in the dabatase and re-associate them with the server login SIDs.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply