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 |
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
 Posted - 2004-11-05 : 08:52:18
|
| I need to transfer an entire database to a new server, including the login permissions. These servers are not connected. How can I do this, where it will also transfer the login users and permissions for each object? Backup/Restore? Attach/Detach? Scripts?TIA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-05 : 15:36:02
|
| http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql2kIf the drive letters and path information for the SQL Server files are identical, then you can follow the above link to move everything over, including master, msdb, and your user database.Tara |
 |
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2004-11-05 : 15:40:40
|
| Hi Tara!The destination server already has some other production DB's on it, so I don't want to move the master database correct? I only want to move my user database over and preserve the permissions. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-05 : 15:48:24
|
| To move the logins, use sp_helprevlogin. Derrick has posted on a copy of it in the forums. Permissions are in the user database so you don't have to worry about that.Tara |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2004-11-05 : 16:10:37
|
| When I searched for sp_help_revlogin or sp_helprevlogin under forums it timed out.Is this what he is talking about? http://support.microsoft.com/kb/246133That's the problem I've had in the past, where I attach a database. Even if I have the same user logins established it still orphans my tables/sprocs/etc because the SID's are different. It sounds like sp_help_revlogin will allow me to create a script that will create the logins with the correct SID's. That must mean that SID's cannot be duped between servers. I'll give it a shot. Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-05 : 16:14:01
|
| Yup that's the one.Tara |
|
|
|
|
|
|
trf DB to new server with security/logins