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
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.
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
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.
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!
3 People are following this question.
