Transfer Users from one SQL Server to another including SID’s the easy way

By | February 22, 2013

Before I wrote an article regarding this one but it seem that was the hard way of doing it.  See the article here http://www.macaalay.com/2012/10/11/how-to-synchronize-loginsusers-between-mirrored-servers/ , but recently I was playing with SSIS in Visual Studio and found out that there is an easy way of doing it without complicated scripting and can all be achieved in a matter of 4 easy steps.

This will be really helpful for those Mirroring a lot of databases and synchronizing their users down to SID’s and permissions.  For this to happen you need Visual Studio with Business Intelligence Project Templates attached to it.  Let’s start.

1. Create an Integration Services project 

1 New Project

2. Use the Transfer Login task

2 Transfer Login Task

3. Setup your Connection

3 Connection

4 Connection Properties

4. Set up the Transfer task properties

In this section you since you are mirroring the users credentials from the database its best to use only “AllLoginsFromSelectedDatabases” and choose the databases you need to copy the logins from.

For Mirroring it is important to set “CopySid” to “true” so your application will run seamless without encountering an access denied issue as it will copy the users exactly how it is on the main SQL Server

5 Transfer Properties

see its really easy, why haven’t I used it like this before

Recommended

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.