December 23, 2002 at 11:37 am
I thought log shipping was the way to go when using the popular method of having one SQL 2000 server be production, and then the other SQL Server be the standby also used for ad-hoc queries of not real-time data. However, while the mechanics of log-shipping always worked, I always had problems with the DB users on the L-S box. Since the L-S server keeps the DB in read-only mode, there is no way to resolve the DB logins. Therefore, the SQL DB is rendered useless because I haven't found a way to resolve those users on a read-only DB.
Has anyone dealt with this issue?
Joe Clauson
MCSE
December 23, 2002 at 4:37 pm
What do you mean by resolve? Logins are in master, shouldnt be affected, even if db is readonly you should be able to query/access sysusers.
Andy
December 24, 2002 at 11:13 am
Yes, the users are in the master DB. However, whenever a DB restore is done, the SQL users to that particular DB are "out-of-sync" with the master DB. Those outstanding users must subsequently be dropped from the DB and granted specific DB permission or resolved using the Autofix option. Of course, when any of these are tried with a read-only DB, they fail.
Joe
December 24, 2002 at 1:15 pm
Gotcha. Running sp_change_users_login does try to update sysusers which would fail if the db was read only. No reason you can't reverse the process yourself, update sysxlogins so that the id there matches the one from your restore. If you do this it will break other db's on the standby by that use any of the affected logins, though you can them fix them using sp_change_users_login.
Andy
January 3, 2003 at 5:17 pm
Thanks for the info. Now, how would I go about doing this in reverse? I'm not sure how I would synchronize two different syslogin tables from two different servers.
January 3, 2003 at 5:58 pm
Not sure what you mean - something like you've got data from Server A and Server B both going to Server C? If you've got the same login on A and B and they have different SIDs, best thing to do is just make one match the other. For instance if you have a login called Test on A with ID of 1, update sysxlogins on B so that Test has ID =1, then also update sysusers in any db that uses the login. Then you set up Server C to use the same ID's as well, you're out of the login fixing business.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply