Transfer database changing user profile info

I have an SSIS package that executes the Transfer Database process (copy). Whenever it executes, it's removes all the settings for db role and the permissions to the views that were designated in the source db for one particular LOGIN / User.

How can I ensure that this does not happen anymore when the package is executed?

Thanks for help!!

Clayp56

asked Feb 27 '12 at 03:06 PM in Default

clayp56
71 ● 6 ● 8 ● 8

(comments are locked)

1 answer:

Seems like the specific LOGIN/user does not exist on the destination, OR if exists than may be the mapping is not correct. Consider using SSIS Transfer Login task prior to Transfer database task. Here is a detailed example

Moreover, sp_change_users_login could be another option depending upon the nature of the Login (sql login) and environment.

answered Feb 28 '12 at 06:27 AM

Usman Butt
13.8k ● 6 ● 8 ● 14

Tried the SSIS Transfer Login, but the Transfer Database task is being done on the same server and SQL instance. The login seems fine, it's just the database user that corresponds to the login is copied over but the db settings and object permissions do not copy over.

Thanks, clayp56

Feb 28 '12 at 03:39 PM clayp56

You do not need Transfer Database task or any other task. The best approach and the easiest way is backup and restore. You can schedule that as well.

Feb 29 '12 at 05:45 AM Usman Butt

You are correct. However in this case the restore was not an option because of the requirement to have this as a SQL Agent job that could be scheduled. Also, the bak files were named by SQL Server and it was turning out to be a rather tedious task to get this accomplished.

So now we I have the transfer DB in the SSIS package working fine and I added a task in the IS package that has two tsql commands. One for sp_addrolemember to add the user to the proper db role and then GRANT SELECT on the view to the user.

It works great !

Thanks for all the help!

Feb 29 '12 at 02:26 PM clayp56

(comments are locked)