Migrating SQL 2000 to SQL 2008 database

Question

Migrating SQL 2000 to SQL 2008 database

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

avanch-990710 Mr or Mrs. 500 Points: 503 More actions · Answer 1

I am migrating from SQL Server 2000 to 2008. I used the Copy Database Wizard and copied the database over to the new server. We are doing side-by-side upgrade with SQL 2008 installed on a separate box and SQL 2000 in the legacy server. All the logins came over to the new server. However, the user mapping to the database roles and database access did not come over.

Has anyone experienced this or do you have any suggestions how I can get this working. I can login to the database using the sa account because sa is a dbowner. But the reat of the accounts cannot access the database and are not attached to any of the database roles that were migrated over to the new server.

Thank you,

Asha

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

Asha

if you haven't migrated the logins then the database will have orphaned users as you have found. use the following to report orphaned users in the database

USE yourdb

exec sp_change_users_login 'report'

this stored procedure also has an autofix option, check books online for more info or post back if you're still stuck

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

avanch-990710 Mr or Mrs. 500 Points: 503 More actions · Answer 3

Perry,

Do I run this stored proc in SQL 2008? Also do I use the migration wizard to bring all the mapping of the roles to the users and grant database access?

I appreciate your response. Thank you,

Asha

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

either use the stored procedure above in a sql 2008 query window or migrate the users not both

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

avanch-990710 Mr or Mrs. 500 Points: 503 More actions · Answer 5

I noticed if I use the stored procedure I have to map the suers one at a time. Even the statement

exec sp_change_users_login 'Auto_fix' needs parameters to be specified :w00t: I was wondering if there is an automated way as I ahve more than 250 logins.

drbobstone Newbie Points: 3 More actions · Answer 6

I'm a big fan of using a select statement to create a list of other statements i have to run. in this case, we need to run against X users on the database to sync with the login using the sp_change_users_login.

USE my_db;

select 'EXEC sp_change_users_login ''Auto_Fix'', ''' + name + ''', NULL, ''password'';'

from sys.sysusers

where name not like 'db_%' and name not in ('public', 'guest', 'information_schema', 'sys')

This gives me results like this as an output, which I can copy and paste back into SSMS and run. 250 with no copy and paste (or in my case 11 databases of 70-145 users each).

EXEC sp_change_users_login 'Auto_Fix', 'GHUNT', NULL, 'password';

EXEC sp_change_users_login 'Auto_Fix', 'intranet', NULL, 'password';

EXEC sp_change_users_login 'Auto_Fix', 'JBENNETT', NULL, 'password';

EXEC sp_change_users_login 'Auto_Fix', 'JDARBY', NULL, 'password';

EXEC sp_change_users_login 'Auto_Fix', 'JKLINDT', NULL, 'password';