May 13, 2007 at 6:46 pm
Hi,
Can someone help me with user login. I restored msdb for DTS package and found that user for those dts packages is restored also. How do I map the user from previous server to a new server so that I can run dts package without creating a new user?
The servers are on SQL 2000.
Thank you.
May 14, 2007 at 12:41 am
...found that user for those dts packages is restored also...
What do you mean with this ?
- Do you mean a userid used with the connection(s) ?
In that case you shouldn't do anything as long a you don't change the server that the connection points to.
- do you mean the package owner ?
You could port the userid from the original msdb-server using this :
At the original server run this. (It just generates a script to add the logins (passwords are encrypted ! ).)
select 'exec sp_addlogin ['
+ name
+ '],'
, password
, ', @encryptopt=skip_encryption'
from master..sysxlogins
At the new server, run the sp_addlogin for the wanted userid.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 14, 2007 at 7:42 am
As ALZDBA pointed out, you might not want to change the connection. There shouldn't be "users" in a package, but logins that point to a particular server. If you want them to change to the new server, you would want to look at the other server and script out the rights. Passwords can be reset or you can use sp_help_revlogin to script with the password.
May 14, 2007 at 12:45 pm
As you have restore MSDB then see if "sp_change_users_login" is any helpful.
May 15, 2007 at 12:21 am
Use sp_reassign_dtspackageowner to change the owner of the package to an existing owner.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply