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

Question

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

pallavi.unde

SSCommitted

Points: 1987
More actions
December 16, 2011 at 3:10 am

#252160

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

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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 More actions · Answer 2

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: 2939 More actions · Answer 3

Marco V

SSCrazy

Points: 2939

December 16, 2011 at 3:38 am

#1422363

EXEC sp_change_users_login 'Auto_Fix', 'user'

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

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 More actions · Answer 5

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: 2939 More actions · Answer 6

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: 148809 More actions · Answer 7

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: 2939 More actions · Answer 8

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: 15943 More actions · Answer 9

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: 148809 More actions · Answer 10

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 More actions · Answer 11

It's working now 🙂

Thank you...

Thanks & Regards,
Pallavi