Moving SQL 2K db to new SQK 2K Svr

Question

Post reply

Moving SQL 2K db to new SQK 2K Svr

SQLJane

SSC Enthusiast

Points: 131
More actions
August 2, 2006 at 1:46 pm

#115005

Perhaps this approach is incorrect, and if so, let me know what else I can do. I have a database in SERVER1 using mixed mode authentication. The db has 3 users, hence the server has 3 security logins all with the 'public' and 'db_owner' roles. I have done the following:
1) created the same db on SERVER2
2) detached the db from SERVER2 (I want to leave the logins on SERVER2)
3) detached and copied the . mdf/.ldf files from SERVER1 to SERVER2
4) attached the (newly copied) .mdf/.ldf to SERVER2
5) I know have my db moved to SERVER2 with the 3 users listed under the db users, as well as in the security logins.
Problem: In each of the logins' properties 'Database Access' tab, the database is not checked. When I try to check it and select 'db_owner' as a role and save, I get a SQL-DMO error that it's already there. I guess there's some kind of SID for the db?
Question(s) 1: I notice that my db application is still operational--that is, I can connect to the db using one of the sql logins no problem. Can I leave it this way? Is this okay? What kinds of problems might I have in the future?
Question(2) 2: If the answer is no above, how can I repair this? or is there a better way to do what I'm doing?

Thanks for anything.

Viewing 5 posts - 1 through 5 (of 5 total)

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

Prema sambandam SSCommitted Points: 1825 More actions · Answer 1

in query analyzer

use <databasename>

go

exec sp_change_users_login 'Update_one','Username' ,'username'

go

Then go and grant the database access. You will be able to do.

SQLJane SSC Enthusiast Points: 131 More actions · Answer 2

Thanks Grasshopper. I'll try it tomorrow. Can you please tell me what you mean by 'go and grant the database access'? Do you mean use sp_grantdbaccess?

Prema sambandam SSCommitted Points: 1825 More actions · Answer 3

go to security-> logins.

select username. right click -> properties

and then select the database and the grant the database access . You can do that now.

SQLJane SSC Enthusiast Points: 131 More actions · Answer 4

Hey, wasn't your name "grasshopper" the other day???

I did the sp_change_users_login trick and it worked perfectly!! Thank you.

There was another post from (you?) saying I could grant the database access from the EM. Yes--after executing the stored proc to change the login I could see that the user was granted db_owner rights to the database. Previously I got an error when I tried to set it that way.

Thanks so much for your help.