How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008

  • pallavi.unde

    SSCommitted

    Points: 1987

    I m mentioning the below steps what i did

    1. MSSQL 2008 installed

    2. Took Backup of databases from MSSQL 2005 and restored it in MSSQL 2008.

    3. then run the script of revlogin that i mentioned before.

    4. logins created for users.

    Problems i faced :-

    1. the users to which i gave all access like sa they can access any database no problem with them.

    2. but the users to which i gave limited permission on limited databases and tables..that account user are not able to access any database.

    3. then i tried ti give the permission to that user which was exist in MSSQL 2005... but that is also giving error like that ''the user is already exist''

    i think the process i followed was wrong... pls suggest me the solution.

    Thanks & Regards,
    Pallavi

  • John Mitchell-245523

    SSC Guru

    Points: 148306

    You need to go into each database and use the ALTER USER command on each user that is affected, in order to associate it with the login on the new server. From memory, the command is something like this:

    ALTER USER [MyDomain\MyAccount] WITH LOGIN [MyDomain\MyAccount]

    John

  • pallavi.unde

    SSCommitted

    Points: 1987

    i tries this

    ALTER USER [ram] WITH LOGIN [ram]

    but it giving following error :-

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'ram'.

    Thanks & Regards,
    Pallavi

  • Marco V

    SSCrazy

    Points: 2909

    EXEC sp_change_users_login 'Auto_Fix', 'user'

  • John Mitchell-245523

    SSC Guru

    Points: 148306

    EXEC sp_change_users_login 'Auto_Fix', 'user'

    That syntax is deprecated and you should avoid using it where possible.

    i tries this

    ALTER USER [ram] WITH LOGIN [ram]

    but it giving following error :-

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'ram'.

    I typed "ALTER LOGIN" into my favourite search engine and I got lots of results telling me the exact syntax. You could do the same.

    John

  • pallavi.unde

    SSCommitted

    Points: 1987

    it's giving o/p :-

    The number of orphaned users fixed by updating users was 0.

    The number of orphaned users fixed by adding new logins and then updating users was 0.

    Thanks & Regards,
    Pallavi

  • Marco V

    SSCrazy

    Points: 2909

    if you now check the properties of the user on the database it will have his login mapped now

    and should be working.

    Only SQL logins will be not be mapped to their database users when migrating the database.

  • John Mitchell-245523

    SSC Guru

    Points: 148306

    Marco V (12/16/2011)


    if you now check the properties of the user on the database it will have his login mapped now

    What, even though there were 0 users fixed? My guess is that the OP has taken it literally and tried to change the user "user". Like I said, though, don't use this - it's deprecated. ALTER USER is the preferred command, and has the advantage that it works for Windows as well as SQL logins.

    John

  • Marco V

    SSCrazy

    Points: 2909

    John Mitchell-245523 (12/16/2011)


    Marco V (12/16/2011)


    if you now check the properties of the user on the database it will have his login mapped now

    What, even though there were 0 users fixed? My guess is that the OP has taken it literally and tried to change the user "user". Like I said, though, don't use this - it's deprecated. ALTER USER is the preferred command, and has the advantage that it works for Windows as well as SQL logins.

    John

    true, iam still using it as a habit from the past

    anyways his typo in the alter login

    ALTER USER [ram] WITH LOGIN = [ram]

    Edit remove the []

    ALTER USER ram WITH LOGIN = ram

  • Divine Flame

    SSCoach

    Points: 15941

    I think you are facing Orphan Users Problem.

    1. You had to generate the logins script on SQL Server 2005 with Sp_HelpRevLogin.

    2. Once generated you had to run that script on SQL Server 2008.

    This process creates the logins with the same SIDs on the new server so that you don't face this problem on your new server. If you havn't executed this process in above said manner then you are most likely to face this.

    In that case, SP_Change_Users_Login OR ALTER LOGIN are your options. You can do any of following:

    USE YourDatabaseName

    GO

    Execute SP_Change_Users_Login 'Update_One','UserName','LoginName'

    OR

    USE YourDatabaseName

    GO

    ALTER USER UserName WITH LOGIN = LoginName

    You can read more here.


    Sujeet Singh

  • John Mitchell-245523

    SSC Guru

    Points: 148306

    Marco V (12/16/2011)


    anyways his typo in the alter login

    ALTER USER ram WITH LOGIN = ram

    Yes indeed. It was an error in the code that I posted, but I did make clear that I was doing it from memory. The OP got a syntax error but made no attempt to find out what the correct syntax should be. That's why I don't post back with the correct syntax - the best way for most people to learn is by doing it for themselves.

    John

  • pallavi.unde

    SSCommitted

    Points: 1987

    It's working now 🙂

    Thank you...

    Thanks & Regards,
    Pallavi

Viewing 12 posts - 1 through 12 (of 12 total)

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