April 22, 2015 at 3:01 am
Hi,
I am restoring a database (using .bak file) using Execute SQL Task in an SSIS Package successfully .
But after restoring the database I am loosing the existing users/logins on the target database.
I have to capture all the users/logins before I am restoring,once the restoration is completed I have to set those users/logins back.
I am not having good knowledge on these DBA activities.Please let me know how can I achieve this.
Regards,
Rocky
April 22, 2015 at 9:17 am
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply