I wrote an article regarding this one earlier, but it seems that was the hard way of doing it. See the article here, 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
2. Use the Transfer Login Task
3. Setup your Connection
4. Set Up the Transfer Task Properties
In this section, since you are mirroring the users credentials from the database, it's 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 seamlessly without encountering an access denied issue as it will copy the users exactly as it is on the main SQL Server.
See it's really easy, why haven’t I used it like this before?
Filed under: CodeProject, Servers, Tips Tagged: SQL Server, SSIS
