none
Import/Export User/Login Credentials and Passwords

    Question

  • Hi,

    I am having problems exporting and importing user/login credentials and passwords between two SQL 2005 database engines.  I am using the built in scripts (right click on login, choose script login as -> create to -> clipboard).  When I run this script against the target enigne, the login is created, but the password does not work when tested.  I have to manually enter the correct password before it would work (at least that is the only change I do for it to work).

    Also, the User Mappings always disappear and I have to enter this manually.

    Any help would be greatly appreciated.

    Thanks!
    Wednesday, July 29, 2009 2:07 PM

Answers

  • User Mapping is maintained at the database level by SID so if you create the logins with the scripts and the SID stays the same the user mapping happens automatically when the database is restored or attached to the new server since the user in the database matches the SID of the login being transfered.

    The Server Roles were part of the SQL 2000 scripts but weren't included in the 2005 version for whatever reason.  You can easily add it by doing something like the following though:

    select 'exec master.dbo.sp_addsrvrolemember @loginame=''' + member.name + ''', @rolename=''' + roles.name + ''''
    from sys.server_role_members
    join sys.server_principals roles on role_principal_id = roles.principal_id
    join sys.server_principals member on member_principal_id = member.principal_id





    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by Simatics Wednesday, July 29, 2009 10:40 PM
    Wednesday, July 29, 2009 3:29 PM

All replies

  • The best way to do this is to script the logins with the associated SID using the correct scripting procedure from Microsoft:

    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=TransferLogins&referringTitle=Home

    I've never done it any other way, and I've never had problems.

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Wednesday, July 29, 2009 3:15 PM
  • Hi,

    thanks for the reply Jonathan! 

    That solved part of the problem.  I just discovered that script and was about to post a response to say that it worked for creating the ID and keeping the password.  However, it loses the Sever Roles and User Mapping configuration.
    Wednesday, July 29, 2009 3:21 PM
  • User Mapping is maintained at the database level by SID so if you create the logins with the scripts and the SID stays the same the user mapping happens automatically when the database is restored or attached to the new server since the user in the database matches the SID of the login being transfered.

    The Server Roles were part of the SQL 2000 scripts but weren't included in the 2005 version for whatever reason.  You can easily add it by doing something like the following though:

    select 'exec master.dbo.sp_addsrvrolemember @loginame=''' + member.name + ''', @rolename=''' + roles.name + ''''
    from sys.server_role_members
    join sys.server_principals roles on role_principal_id = roles.principal_id
    join sys.server_principals member on member_principal_id = member.principal_id





    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by Simatics Wednesday, July 29, 2009 10:40 PM
    Wednesday, July 29, 2009 3:29 PM
  • Hi,

    this is a PERFECT answer.  It worked flawlessly.

    Just to be clear what I did:

    1. I backed up the database (full data backup) from the source.
    2. I ran the script as indicated by microsoft to produce a script (script A): http://support.microsoft.com/kb/918992/
    3. I ran the following script and saved each row of the results as one script (script B):

    select 'exec master.dbo.sp_addsrvrolemember @loginame=''' + member.name + ''', @rolename=''' + roles.name + ''''
    from sys.server_role_members
    join sys.server_principals roles on role_principal_id = roles.principal_id
    join sys.server_principals member on member_principal_id = member.principal_id

    4. I restored the full data backup to the destination.
    5. I ran script A against the destination.
    6. I ran script B against the destination.



    What this did was built the logins, and kept the password, Server Roles and User Mapping confiugrations for all the logins.

    Brilliant and flawless. Thanks Jonathan!

    Wednesday, July 29, 2009 10:45 PM