Users/Roles/Schemas

0 pts.
Tags:
SQL
I'm trying to find some documentation or procedures that will allow me to xfer existing users' roles, etc from sql2000 to sql2005. When our sql 2005 database was restored on a new server (from sql2000), all of the database users were orphaned. I need to create identical server logins and then make the logins database users. It is also important to understand the significance of the new schema concept. The Microsoft SQL2005UpgradeTechRef.doc white paper does not explain any of this. Ultimately, I would like to create a script for the above scenario once a sql2005 db is restored from a sql2000 db. Could someone please help me out on this?
ASKED: November 27, 2006  9:46 AM
UPDATED: November 29, 2006  10:50 AM

Answer Wiki

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

Good morning.

I’m not exactly sure if I followed your description correctly, so let me see if I have this right:

You currently have a SQL2000 server running. You want to upgrade those databases to SQL2005 on a different box. And the issue you’re having is orphaned local Windows accounts after the upgrade.

First off, unless you have a method of copying the SAM and having that work on the new server with a different SID, you’ll need to do a full copy of the old server and restore onto the new server. That will bring over the local Windows accounts. Otherwise, I would suggest (if this is an option, since I don’t know your environment) making the local accounts domain accounts. The you can do a fresh build on the new server, install SQL2005, and migrate the accounts over.

Otherwise, you’ll have to build the new server, manually create all of the local accounts, install SQL2005, then migrate the databases over. I’m not terribly Windows savvy, but I’m not aware of any way to script the automation of copying the Windows accounts from one server to another.

I hope this helps.

Jeff

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