October 19, 2007 at 11:28 am
Here is my situation... we are using log shipping to keep our DR databases in sync with production (most of these are currently sql2000). We rely heavily on SQL logins for our applications and would like to keep the DR versions of the SQL logins in sync with prod. As I see it, there are basically 3 possible scenarios:
1. New SQL login created on prod
2. SQL login deleted on prod
3. Password for login changed
What I would like to do is sync the logins at least daily. I consider #2 to be the least import. #1 I have been able to use the sp_help_revlogin SP to generate the t-sql to run on the DR side to create a new user and assume that I can just schedule a job to run the code. The real trouble is with #3 - I originally wanted to drop all logins and just recreate them, but I can not drop them because they are associated with users. I did come across a post somewhere that suggested using an modified version of sp_password that allows for the encrypted text to be passed, but my instances are not configured to allow ad hoc updates to system tables and does not seem to be the best practice. Any thoughts on a reasonable solution or am I approaching this incorrectly???
Any help will be greatly appreciated!!!
Thanks - J.
-- SORRY Should have been put into SQL 2000 Admin vs. 2005. If the Mod could move it that would be great.
October 19, 2007 at 12:43 pm
I don't understand why you cannot drop them and recreate them.
October 19, 2007 at 1:31 pm
First, let me start with - I am a fairly new DBA, so I may have missed something...
What I am running into is if I attempt to drop a login and the login has a user associated with it in a database - then it fails. If there is a way that I can get past that with out dropping and recreating every user - I am all for it. Log shipping seems to maintain the users perfectly well and I did not really want to open that can of worms if it is not necessary. I just wanted be able to update the password of existing SQL logins (another reason to user Windows Auth!!!).
October 19, 2007 at 1:39 pm
OK, I missed that you are using SQL 2000. It would work in 2005 even if the logins are mapped to users in the database. You should just upgrade and then it will work (easier said than done) 🙂
I'm trying to think of something that would work on 2000, but I'm drawing a blank. If I think of something, I'll let you know.
October 19, 2007 at 3:17 pm
October 22, 2007 at 1:05 pm
I did use this article as a blueprint on getting the logins there the first time. I will take another look at this in detail later this week (out of the office:) My bigger issue is that after the login exists on the DR side and I have a user with the same SID in any of the databases - I will not be able to update the password if it has changed on the production side.
Thanks - Jeremy.
October 23, 2007 at 4:34 am
As per the article NoelD referred you to, the DTS transfer login task is your best option here.
You will just have to run sp_change_users_login 'update_one' for each user on failover.
Bet you wish you had windows authenticated users! (in a single group if poss)
---------------------------------------------------------------------
October 24, 2007 at 8:10 pm
Take apart sp_helprevlogin to understand how it is extracting the password. Use that to update the password on the second server if the password has changed.
K. Brian Kelley
@kbriankelley
October 25, 2007 at 5:40 am
Thanks all - now that I am back in the office I will give these suggestions a shot.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply