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 |
|
aleceiffel
Starting Member
1 Post |
 Posted - 2005-10-26 : 17:15:18
|
| I have an application that runs off an sql server 2000 sp3 database. I have taken a backup (.bak file) of the production database and restored it to another server for testing purposes. the users cannot login to the new server because the logins have not been transfered to the new server. I am looking to transfer the logins using the procedure outlined at the following link: http://support.microsoft.com/kb/246133/ under the header "Create and Run Stored Procedures in the Master Database". what I need to know is will this procedure affect the production server in any way? (I can't have that) and is there anything else I need to do after this to let the users login to the test server with the same name and passwords?thanks for any help |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-10-26 : 19:13:36
|
| One thing to look at is using a DTS package. There is a DTS task called "transfer logins task". You basically just define a source and destination server. (This will not affect the source server in any noticable way)After you get the logins on the target server you will probably need to reassociate the users on the restored db with the transfered logins. You can use sp_change_users_login (See Books Online).Did you try to search this site for "transfer logins"? I'm sure there have been some good stuff posted about it also.Be One with the OptimizerTG |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-26 : 19:22:58
|
| I prefer to use this:http://www.windowsitpro.com/SQLServer/Article/ArticleID/16090/16090.htmlJust run this on the source server, copy the output to your test server, then run the output. Your sids will be in synch, so you won't need to run sp_change_users_login. It does not impact the production server. It retains the passwords.Tara |
|
|
|
gkrishn
Starting Member
16 Posts |
Posted - 2005-10-27 : 13:21:30
|
| generate the username/password as mentioned in that websitehttp://support.microsoft.com/kb/246133/chose only users who need access to your DB (no need to copy all the users of other DBs also ) and execute the script in TEST . this will recreate the users in your TEST .just use sp_change_users_login 'report' to check any inconsistancy in logins.If you get any output just fix it (how to fix it , i will tell you next post :) )-Rajiv |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-27 : 15:04:52
|
| If you use the method in the link that I posted, then you don't need to bother with sp_change_users_login. It's a much simpler method.Tara |
|
|
|
|
|
|
transfering logins to a new sql server