Link to home
Create AccountLog in
Avatar of Matthew Cioffi
Matthew CioffiFlag for United States of America

asked on

DR setup for MSSQL database and logins

Hi Folks,

We have a vendor application the runs on SQL server, so far we have planned out the app recovery, no big deal.  Just restore the relevant directories and away we go there.

This is SQL server 2003.

The database overall is pretty easy, a simple restore, drop a login, user and recreate has one test user back in and working.

The problem I have is keeping the users up to date between the prod instance and the DR instance.  What I need is a method to drop and add back users to the sql instance and users to the database to reflect the prod instance.

If it was static I would use the sp_help_revlogin and include the relevant users in the restore document.  Since there is a good chance that passwords will change and users will be added or dropped I need a way to keep them up to date.  The assumption is that the PROD environment will not be available when we go to use this DR environment.  

Can I include something in a backup that I can use?
Is there a native way in SQL that I'm missing?

Any suggestions that you have would be greatly appreciated.
SOLUTION
Avatar of oleggold
oleggold
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Matthew Cioffi

ASKER

Hi Oleggold,
None of these seems to be quite on target.  This is not a share point instance.  What I'm concerned about is making sure once we get to the DR site the logins are still usable.  

Once I restore the vendor db the users are there, but the problem is that the logins will probably be outdated.  

What is the best way to ensure that I have the most up to date list of logins and passwords on the DR server?
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
The sql query will give us the ability to link the user back to a login.  We still have a password issue, but we will have to get used to changing them on first login to the app.