March 31, 2011 at 7:28 am
Hello,
We have a 'replica' of an erp application setup in a remote location. The database server uses SQL 2008. All databases are in sync using log shipping, which is set for every six hours as transactions are low and the business is happy with this level of recovery/loss. The server uses built in sql users for security, and not domain logins unfortunately. I have successfully transferred sql users and passwords across to the secondary server using sp_help_revlogin.
The problem is keeping users and passwords in sync. The application requires users to change passwords every three months, and so we need to get those changes, and any additional users transferred to the secondary server on a regular basis - how can this be done? I cant use sp_help_revlogin as this only creates new users and wont update existing passwords. I'm far from a SQL expert, so all help appreciated!
Thanks in advance!
March 31, 2011 at 8:26 am
What I do is script out the logins and agent jobs (which can also change BTW) daily and they get shipped to the standby servers along with the backups and t-logs. One of the steps to bringing a standby server on-line is to execute those two scripts after the databases have been recovered which will re-create the logins. The process is mostly automated but a person needs to manually initiate it and to verify that all the scripts ran without error.
If you don't have total control over the environment along the way you should encrypt the the scripts and decrypt only before you execute them.
The probability of survival is inversely proportional to the angle of arrival.
March 31, 2011 at 10:32 am
Hi
i use BCP to dump syslogins to a file, copy it to DR server and then import
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply