x

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
more ▼

asked Feb 27, 2012 at 03:06 PM in Default

clayp56 gravatar image

clayp56
71 9 9 9

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.
more ▼

answered Feb 28, 2012 at 06:27 AM

Usman Butt gravatar image

Usman Butt
13.9k 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, 2012 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, 2012 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, 2012 at 02:26 PM clayp56
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x929
x84
x4

asked: Feb 27, 2012 at 03:06 PM

Seen: 1133 times

Last Updated: Feb 29, 2012 at 02:48 PM