InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 06:01:26 GMT20http://www.sqlservercentral.com/Forums/Topic1222916-323-1.aspxIt's working now :)Thank you...Fri, 16 Dec 2011 04:26:52 GMTpallavi.undehttp://www.sqlservercentral.com/Forums/Topic1222916-323-1.aspx[quote][b]Marco V (12/16/2011)[/b][hr]anyways his typo in the alter loginALTER USER ram WITH LOGIN [b]= [/b]ram[/quote]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.JohnFri, 16 Dec 2011 04:11:49 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1222916-323-1.aspxI think you are facing [u]Orphan Users Problem[/u].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, [b]SP_Change_Users_Login[/b] OR [b]ALTER LOGIN[/b] are your options. You can do any of following:[code="sql"]USE YourDatabaseNameGOExecute SP_Change_Users_Login 'Update_One','UserName','LoginName'[/code]OR[code="sql"]USE YourDatabaseNameGOALTER USER UserName WITH LOGIN = LoginName[/code]You can read more [b][url=http://msdn.microsoft.com/en-us/library/ms175475.aspx]here[/url][/b].Fri, 16 Dec 2011 04:11:08 GMTDivine Flamehttp://www.sqlservercentral.com/Forums/Topic1222916-323-1.aspx[quote][b]John Mitchell-245523 (12/16/2011)[/b][hr][quote][b]Marco V (12/16/2011)[/b][hr]if you now check the properties of the user on the database it will have his login mapped now[/quote]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[/quote]true, iam still using it as a habit from the pastanyways his typo in the alter loginALTER USER [ram] WITH LOGIN [b]= [/b][ram]Edit remove the []ALTER USER ram WITH LOGIN [b]= [/b]ramFri, 16 Dec 2011 04:04:50 GMTMarco Vhttp://www.sqlservercentral.com/Forums/Topic1222916-323-1.aspx[quote][b]Marco V (12/16/2011)[/b][hr]if you now check the properties of the user on the database it will have his login mapped now[/quote]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.JohnFri, 16 Dec 2011 03:59:35 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1222916-323-1.aspxif you now check the properties of the user on the database it will have his login mapped nowand should be working.Only SQL logins will be not be mapped to their database users when migrating the database.Fri, 16 Dec 2011 03:52:42 GMTMarco Vhttp://www.sqlservercentral.com/Forums/Topic1222916-323-1.aspxit'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.Fri, 16 Dec 2011 03:44:26 GMTpallavi.undehttp://www.sqlservercentral.com/Forums/Topic1222916-323-1.aspx[quote]EXEC sp_change_users_login 'Auto_Fix', 'user'[/quote]That syntax is deprecated and you should avoid using it where possible.[quote]i tries this ALTER USER [ram] WITH LOGIN [ram]but it giving following error :-Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'ram'. [/quote]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.JohnFri, 16 Dec 2011 03:43:45 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1222916-323-1.aspxEXEC sp_change_users_login 'Auto_Fix', 'user'Fri, 16 Dec 2011 03:38:27 GMTMarco Vhttp://www.sqlservercentral.com/Forums/Topic1222916-323-1.aspxi tries this ALTER USER [ram] WITH LOGIN [ram]but it giving following error :-Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'ram'.Fri, 16 Dec 2011 03:34:01 GMTpallavi.undehttp://www.sqlservercentral.com/Forums/Topic1222916-323-1.aspxYou 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:[code="sql"]ALTER USER [MyDomain\MyAccount] WITH LOGIN [MyDomain\MyAccount][/code]JohnFri, 16 Dec 2011 03:18:46 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1222916-323-1.aspxI m mentioning the below steps what i did1. MSSQL 2008 installed2. 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.Fri, 16 Dec 2011 03:10:44 GMTpallavi.unde