SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
pallavi.unde
pallavi.unde
SSC-Addicted
SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)

Group: General Forum Members
Points: 461 Visits: 170
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
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33339 Visits: 16649
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
pallavi.unde
SSC-Addicted
SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)

Group: General Forum Members
Points: 461 Visits: 170
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
Marco V
SSC Eights!
SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)

Group: General Forum Members
Points: 864 Visits: 569
EXEC sp_change_users_login 'Auto_Fix', 'user'
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33339 Visits: 16649
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
pallavi.unde
SSC-Addicted
SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)SSC-Addicted (461 reputation)

Group: General Forum Members
Points: 461 Visits: 170
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
Marco V
SSC Eights!
SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)

Group: General Forum Members
Points: 864 Visits: 569
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
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33339 Visits: 16649
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
Marco V
SSC Eights!
SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)

Group: General Forum Members
Points: 864 Visits: 569
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
Divine Flame
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4269 Visits: 2816
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search