April 29, 2002 at 2:41 pm
I have inherited three servers, one production and two for development/testing. I am trying to sync up users in the master db, on the development/test servers, to all have the same SID that they have in production . I've tried DTS and using the scripting function from enterprise manager but cannot generate the same SID (it does create the users). Anyone have any ideas? I would also need to run this option periodically, since new users could be added to production that would also need access to test. Any and all help would be appreciated. Thanks.
Edited by - tosscrosby on 04/29/2002 2:54:58 PM
Terry
April 29, 2002 at 7:54 pm
DTS will work if you have SQL2K and use the move objects part. There are a couple ways to do it other than that. It's just data, so you can BCP it or move it with a query if you set up a linked server. The only time it matters if the SID is different is if you're actually going to move the MDF from one server to the other. If/when you do that, it's easy enough to run sp_change_users_login to change the SID in the db to the one in sysxlogins.
Possibly overkill, but Red-Gate has a sync tool that you can use for stuff like this (Data Compare) and they have a new product I haven't looked at yet called Server Compare that might be worth a look.
Andy
May 7, 2002 at 1:00 pm
What if they do plan on moving the MDF from one server to another???
Terry Crosby
Terry
May 7, 2002 at 1:51 pm
If/when you move the mdf the issues are about the same. If you already have the logins on the new server you'll have to change the SID's using sp_change_users_login - without it the users will be orphaned. If the logins don't exist you can add them manually and then run sp_change_users_login, or use one of the methods I mentioned earlier to add them with the same SID as on the original server.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply