• I have done something similar in the past. It can get tricky depending on the roles that different logins have though.

    Here is what my restore looks like.

    I have a foreach loop to go through the backup files to restore them.

    The first step inside is an execute SQL task, designed to create a query that will add the necessary users. This assumes that each user already has a login set up on the database server (which they should unless they are orphans from the original), and that each login being created this way needs only the db_datareader role for this database. You can add in other roles easily if every user should have them, but if you need different roles for different users this gets much tougher

    The SQL looks like this:

    select STUFF( ( select ' CREATE USER [' + dp.name + '] FOR LOGIN [' +dp.name + '] WITH DEFAULT_SCHEMA=[dbo]

    EXEC sp_addrolemember @rolename = N''db_datareader'', @membername = N''' + dp.name + ''';

    ' from sys.database_principals dp

    join sys.syslogins sl

    on dp.sid = sl.sid

    where dp.name like 'YourDomainHere\%' FOR XML PATH(''),TYPE).value('.','VARCHAR(8000)'),1,1,'') Name

    If you have SQL server auth users or multiple domains you may have to change the where clause in the above. I put the output of this task into a string variable.

    After doing this I do the restore (and associated cleanup tasks)

    Then I delete users from the restored DB using a simple data flow. The source has this query :

    select dp.name from sys.database_principals dp

    where dp.name like 'YourDomainHere\%'

    And that links to an OLE DB Command transformation that runs this

    exec dbo.sp_revokedbaccess ? WITH RESULT SETS NONE

    With the parameter linking to the field from the data flow

    Finally, I have an execute SQL task that has an expression to use the variable I created in step one as its SQL Statement Source.