Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008 Expand / Collapse
Author
Message
Posted Friday, December 16, 2011 3:10 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 14, 2013 5:35 AM
Points: 191, 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
Post #1222916
Posted Friday, December 16, 2011 3:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 5,420, Visits: 10,076
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
Post #1222919
Posted Friday, December 16, 2011 3:34 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 14, 2013 5:35 AM
Points: 191, 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
Post #1222925
Posted Friday, December 16, 2011 3:38 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:32 AM
Points: 462, Visits: 525
EXEC sp_change_users_login 'Auto_Fix', 'user'

Post #1222927
Posted Friday, December 16, 2011 3:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 5,420, Visits: 10,076
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
Post #1222928
Posted Friday, December 16, 2011 3:44 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 14, 2013 5:35 AM
Points: 191, 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
Post #1222929
Posted Friday, December 16, 2011 3:52 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:32 AM
Points: 462, Visits: 525
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.
Post #1222932
Posted Friday, December 16, 2011 3:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 5,420, Visits: 10,076
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
Post #1222938
Posted Friday, December 16, 2011 4:04 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:32 AM
Points: 462, Visits: 525
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

Post #1222941
Posted Friday, December 16, 2011 4:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:33 AM
Points: 1,325, Visits: 2,591
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
Post #1222944
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse