SQL 2000 migration to SQL 2005 – need to preserve user security

5 pts.
Tags:
SQL Database Security
SQL Server 2000 to SQL Server 2005 upgrade
SQL Server security
SQL Server Upgrade
Hi, I have a customer that is planning on moving to new hardware and performing a SQL upgrade (2000->2005).  I know that our application (FileNet Image Services) is sensitive to what we refer to as an "orphaned" database.  Our app stops working if a database is simply detached/attached to another server because it loses that link between the users in the database and the users in SQL Security.  Once that happens, that users cannot simply be recreated in Security-Logins.  Because of internal IDs the users no longer match and our app stops working.

Any ideas to get around this?



Software/Hardware used:
SQL2000 to SQL2005 migration/upgrade need to preserve user security

Answer Wiki

Thanks. We'll let you know when a new response is added.

Google for sp_help_revlogin which will allow you to script out the logins with the SIDs so that when you create the logins on the new server the SIDs will match. You should also look at sp_change_users_login, which will reassign logins to users after moving the database to another server.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following