question

clayp56 avatar image
clayp56 asked

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
ssislogintransfer
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Usman Butt avatar image
Usman Butt answered
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][1] [1]: http://www.sql-server-performance.com/2009/transfer-logins-transfer-database-task-ssis/ Moreover, `sp_change_users_login` could be another option depending upon the nature of the Login (sql login) and environment.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

clayp56 avatar image clayp56 commented ·
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
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
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.
0 Likes 0 ·
clayp56 avatar image clayp56 commented ·
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!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.