restore db of 2000 to 2005 logins

I'm not sure why I can't find any info on this issue, because it is blaring.
When backup a 2000 db and restore it on Sql Server 2005, logins are not
created at the server level. And then when I try to create the logins
manually at the server level, I get "login already exists". Then as I've done
with db migrations in the past, I battle with deleting logins at the db level
because they are assigned to schemas.
What is the best way to get logins at the db level to the server level?
Thanks.HI Jamesm,
Thank you for using MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: After you backup
your SQL Server 2000 database to another SQL Server 2005 instance, the
logins didn't created in the Server side and you get the "User or role '%s'
already exists in the current database." error when you want to grant the
permission to the database. If I misunderstood your concern, please feel
free to let me know.
This is an orphaned users scenario. To resolve this issue, please use the
sp_change_users_login system stored procedure.
USE <database_name>;
GO
sp_change_users_login @.Action='update_one',
@.UserNamePattern='<database_user>', @.LoginName='<login_name>';
GO
You may refer the following KB article:
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/kb/274188/en-us
Also, here are some articles may be helpful:
Troubleshooting Orphaned Users
http://msdn2.microsoft.com/en-us/library/ms175475(SQL.90).aspx
sp_change_users_login (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms174378.aspx
How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/kb/246133/en-us
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi
http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx
"Wei Lu" <weilu@.online.microsoft.com> wrote in message
news:fyTtY7NaGHA.932@.TK2MSFTNGXA01.phx.gbl...
> HI Jamesm,
> Thank you for using MSDN Managed Newsgroup Support.
> From your description, my understanding of this issue is: After you backup
> your SQL Server 2000 database to another SQL Server 2005 instance, the
> logins didn't created in the Server side and you get the "User or role
> '%s'
> already exists in the current database." error when you want to grant the
> permission to the database. If I misunderstood your concern, please feel
> free to let me know.
> This is an orphaned users scenario. To resolve this issue, please use the
> sp_change_users_login system stored procedure.
> USE <database_name>;
> GO
> sp_change_users_login @.Action='update_one',
> @.UserNamePattern='<database_user>', @.LoginName='<login_name>';
> GO
> You may refer the following KB article:
> PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
> http://support.microsoft.com/kb/274188/en-us
> Also, here are some articles may be helpful:
> Troubleshooting Orphaned Users
> http://msdn2.microsoft.com/en-us/library/ms175475(SQL.90).aspx
> sp_change_users_login (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms174378.aspx
> How to transfer logins and passwords between instances of SQL Server
> http://support.microsoft.com/kb/246133/en-us
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Awesome! Thank you for help.
"Wei Lu" wrote:
> HI Jamesm,
> Thank you for using MSDN Managed Newsgroup Support.
> From your description, my understanding of this issue is: After you backup
> your SQL Server 2000 database to another SQL Server 2005 instance, the
> logins didn't created in the Server side and you get the "User or role '%s'
> already exists in the current database." error when you want to grant the
> permission to the database. If I misunderstood your concern, please feel
> free to let me know.
> This is an orphaned users scenario. To resolve this issue, please use the
> sp_change_users_login system stored procedure.
> USE <database_name>;
> GO
> sp_change_users_login @.Action='update_one',
> @.UserNamePattern='<database_user>', @.LoginName='<login_name>';
> GO
> You may refer the following KB article:
> PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
> http://support.microsoft.com/kb/274188/en-us
> Also, here are some articles may be helpful:
> Troubleshooting Orphaned Users
> http://msdn2.microsoft.com/en-us/library/ms175475(SQL.90).aspx
> sp_change_users_login (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms174378.aspx
> How to transfer logins and passwords between instances of SQL Server
> http://support.microsoft.com/kb/246133/en-us
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||That code is now in the KB as well at:
http://support.microsoft.com/?id=246133
Kind of a mess though. They updated the SQL 2000 article and
code for sp_help_revlogin with the version for 2005 - same
code on Laurentiu's blog.
I don't know where the 2000 version of sp_help_revlogin is
at anymore though.
-Sue
On Wed, 26 Apr 2006 11:57:18 +0300, "Uri Dimant"
<urid@.iscar.co.il> wrote:
>Hi
>http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx
>
>
>"Wei Lu" <weilu@.online.microsoft.com> wrote in message
>news:fyTtY7NaGHA.932@.TK2MSFTNGXA01.phx.gbl...
>> HI Jamesm,
>> Thank you for using MSDN Managed Newsgroup Support.
>> From your description, my understanding of this issue is: After you backup
>> your SQL Server 2000 database to another SQL Server 2005 instance, the
>> logins didn't created in the Server side and you get the "User or role
>> '%s'
>> already exists in the current database." error when you want to grant the
>> permission to the database. If I misunderstood your concern, please feel
>> free to let me know.
>> This is an orphaned users scenario. To resolve this issue, please use the
>> sp_change_users_login system stored procedure.
>> USE <database_name>;
>> GO
>> sp_change_users_login @.Action='update_one',
>> @.UserNamePattern='<database_user>', @.LoginName='<login_name>';
>> GO
>> You may refer the following KB article:
>> PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
>> http://support.microsoft.com/kb/274188/en-us
>> Also, here are some articles may be helpful:
>> Troubleshooting Orphaned Users
>> http://msdn2.microsoft.com/en-us/library/ms175475(SQL.90).aspx
>> sp_change_users_login (Transact-SQL)
>> http://msdn2.microsoft.com/en-us/library/ms174378.aspx
>> How to transfer logins and passwords between instances of SQL Server
>> http://support.microsoft.com/kb/246133/en-us
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>|||Hi,Sue
> I don't know where the 2000 version of sp_help_revlogin is
> at anymore though.
If you want I can send you
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:d9tv42hmgtd3m5mjpevthjv0fqk519c075@.4ax.com...
> That code is now in the KB as well at:
> http://support.microsoft.com/?id=246133
> Kind of a mess though. They updated the SQL 2000 article and
> code for sp_help_revlogin with the version for 2005 - same
> code on Laurentiu's blog.
> I don't know where the 2000 version of sp_help_revlogin is
> at anymore though.
> -Sue
> On Wed, 26 Apr 2006 11:57:18 +0300, "Uri Dimant"
> <urid@.iscar.co.il> wrote:
>>Hi
>>http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx
>>
>>
>>"Wei Lu" <weilu@.online.microsoft.com> wrote in message
>>news:fyTtY7NaGHA.932@.TK2MSFTNGXA01.phx.gbl...
>> HI Jamesm,
>> Thank you for using MSDN Managed Newsgroup Support.
>> From your description, my understanding of this issue is: After you
>> backup
>> your SQL Server 2000 database to another SQL Server 2005 instance, the
>> logins didn't created in the Server side and you get the "User or role
>> '%s'
>> already exists in the current database." error when you want to grant
>> the
>> permission to the database. If I misunderstood your concern, please feel
>> free to let me know.
>> This is an orphaned users scenario. To resolve this issue, please use
>> the
>> sp_change_users_login system stored procedure.
>> USE <database_name>;
>> GO
>> sp_change_users_login @.Action='update_one',
>> @.UserNamePattern='<database_user>', @.LoginName='<login_name>';
>> GO
>> You may refer the following KB article:
>> PRB: "Troubleshooting Orphaned Users" Topic in Books Online is
>> Incomplete
>> http://support.microsoft.com/kb/274188/en-us
>> Also, here are some articles may be helpful:
>> Troubleshooting Orphaned Users
>> http://msdn2.microsoft.com/en-us/library/ms175475(SQL.90).aspx
>> sp_change_users_login (Transact-SQL)
>> http://msdn2.microsoft.com/en-us/library/ms174378.aspx
>> How to transfer logins and passwords between instances of SQL Server
>> http://support.microsoft.com/kb/246133/en-us
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>|||Thanks...I have both them.
Just seems very odd they wiped out the 2000 version and
replaced the 2005 code in the 2000 KB article.
Should be two different articles....
-Sue
On Thu, 27 Apr 2006 12:12:16 +0300, "Uri Dimant"
<urid@.iscar.co.il> wrote:
>Hi,Sue
>> I don't know where the 2000 version of sp_help_revlogin is
>> at anymore though.
>If you want I can send you
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:d9tv42hmgtd3m5mjpevthjv0fqk519c075@.4ax.com...
>> That code is now in the KB as well at:
>> http://support.microsoft.com/?id=246133
>> Kind of a mess though. They updated the SQL 2000 article and
>> code for sp_help_revlogin with the version for 2005 - same
>> code on Laurentiu's blog.
>> I don't know where the 2000 version of sp_help_revlogin is
>> at anymore though.
>> -Sue
>> On Wed, 26 Apr 2006 11:57:18 +0300, "Uri Dimant"
>> <urid@.iscar.co.il> wrote:
>>Hi
>>http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx
>>
>>
>>"Wei Lu" <weilu@.online.microsoft.com> wrote in message
>>news:fyTtY7NaGHA.932@.TK2MSFTNGXA01.phx.gbl...
>> HI Jamesm,
>> Thank you for using MSDN Managed Newsgroup Support.
>> From your description, my understanding of this issue is: After you
>> backup
>> your SQL Server 2000 database to another SQL Server 2005 instance, the
>> logins didn't created in the Server side and you get the "User or role
>> '%s'
>> already exists in the current database." error when you want to grant
>> the
>> permission to the database. If I misunderstood your concern, please feel
>> free to let me know.
>> This is an orphaned users scenario. To resolve this issue, please use
>> the
>> sp_change_users_login system stored procedure.
>> USE <database_name>;
>> GO
>> sp_change_users_login @.Action='update_one',
>> @.UserNamePattern='<database_user>', @.LoginName='<login_name>';
>> GO
>> You may refer the following KB article:
>> PRB: "Troubleshooting Orphaned Users" Topic in Books Online is
>> Incomplete
>> http://support.microsoft.com/kb/274188/en-us
>> Also, here are some articles may be helpful:
>> Troubleshooting Orphaned Users
>> http://msdn2.microsoft.com/en-us/library/ms175475(SQL.90).aspx
>> sp_change_users_login (Transact-SQL)
>> http://msdn2.microsoft.com/en-us/library/ms174378.aspx
>> How to transfer logins and passwords between instances of SQL Server
>> http://support.microsoft.com/kb/246133/en-us
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>|||I am doing a SQL 2000 > 2005 migration. I plan to just restore
databases from backup, and then run the sp_help_revlogin stored
procedure.
Does anyone know if I need to restore the databases before I run this
script, or if its safe to run the script (which migrates all
logins/passwords from my SQL 2000 server), and then restore the
databases at a later date
Thanks in advance,
-Jsql