User Login

  •  

    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.

     

     

  • ...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

  • 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.

  • As you have restore MSDB then see if "sp_change_users_login" is any helpful.

  • Use sp_reassign_dtspackageowner to change the owner of the package to an existing owner.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply