Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
TimSinnott
Starting Member
48 Posts |
 Posted - 2005-07-01 : 10:52:23
|
| I have a simple SQL Server 2000 database on an older development machine (a laptop). That database has several different Users. I need to copy the database to my new development laptop, and bring its Users and permissions along, too.I'm using Enterprise Manager on each machine. (IMPORTANT: Neither machine is part of a network, and I do not want to connect the machines.)This morning I used a ZipDisk to do a Backup on the older machine, and then Restored on the new machine. This was fast and easy. The tables and stored procedures came over fine. The only drawback is that the Users that show in the database on the old machine do not show up for the database on the new machine.My question is this: Is there an easy way to copy the Users and permissions in this situation?Thanks in advance.Tim |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-07-01 : 10:57:20
|
| Do the users have logins on the destination machine. If so you may want to look at sp_change_users_login.Sean RoussyPlease backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.I am available for consulting work. Just email me though the forum. |
 |
|
|
TimSinnott
Starting Member
48 Posts |
Posted - 2005-07-01 : 11:04:37
|
| Sean - No the Users do not have logins on the destination machine. I'm looking for some way to transfer that info. I guess I could do a Copy/Restore for the master database?Or maybe I could script the Logins/Users somehow?Tim |
|
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-07-01 : 11:40:02
|
| I would not backup and restore master. You will not be able to see your other databases on the destination server.Sean RoussyPlease backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.I am available for consulting work. Just email me though the forum. |
|
|
|
TimSinnott
Starting Member
48 Posts |
Posted - 2005-07-01 : 12:41:04
|
| Yes, good point, Sean. Thanks.Does anybody have any other ideas?Tim |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-07-01 : 13:05:47
|
| Look up sp_addlogin in BOL. All you really need is to supply the SID from your old machine, and you should be set to go. |
|
|
|
TimSinnott
Starting Member
48 Posts |
Posted - 2005-07-01 : 15:24:15
|
| SID?Tim |
|
|
|
TimSinnott
Starting Member
48 Posts |
Posted - 2005-07-01 : 16:42:55
|
Heh, heh.  Well, I was wrong. It turns out that the Users did come over with the Copy/Restore. I just needed to hit Refresh.For the Logins, however, I did in fact need to create those on the destination machine. But the Users and permissions appear to have come over cleanly.My bad.Tim |
|
|
|
|
|
|
|
|
Copying a database with Users