July 7, 2010 at 6:25 am
Hi all,
I am by no means a DBA of the calibre of some of the contributors on this forum, but I've been tasked with the following:
My company supports an application for a security company that requires 100% uptime (the security company monitors 3 of the largest banks in the country, all branches, ATM's, etc). The application they use is a windows desktop app written in C++, and because security was a huge concern for their auditors, the logins work on the following principle:
1. Several roles were created on the server for each classification of user (Admin, Manager, Tech, etc).
2. Each person is created a login in SQL Server itself with a password that he/she logs in to the application on SQL Server, so, in essence, with that user they can log in to SQL Management Studio from any machine and will have similar rights as in the application (probably not the best way to do it, but this was done a LOONNNNGGGGG time ago)
There is a standby server that serves as a subscriber for the databases's data (also SQL 2008), which is being set up to be used in the event of a catastrophic crash on the main server (i.e. being set up with the server apps being used, radio listeners, etc).
However, as I experienced a while ago when one of the secondary sites crashed, recovering the users is a huge problem, as there are no real dedicated DBA's to perform these tasks (script users, etc), since the users are stored in SQL itself.
Now, my question is, is there a way I can "replicate" these users and their passwords and permissions to the backup server automatically, using some sort of SQL tool, even replication itself, so that if the main server goes down, then at least switch-over will be less of a headache?
Currently, if the server should go down, we'll have to re-create each user by hand again (ones that are not scripted), which will take forever since there are 100+ call center operators at the main site?
Thanks a lot for any replies!!!!
The H..............................................................................
July 7, 2010 at 7:27 am
google sp_help_revlogin, that does what you want by script.
there is a transfer logins task in SSIS, but only use that if all your ids are windows authenticated.
---------------------------------------------------------------------
July 7, 2010 at 7:29 am
Hi George,
Thanks, will do. I checked that SSIS Transfer Logins task, but it still requires you to change the password, which defeats the point in my scenario.
Thanks again.
July 7, 2010 at 7:44 am
Yes. for SQL authenticated IDs it changes the password to a random value for security reasons, which makes it pointless in my view. Its good for windows authenticated IDS though.
---------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply