Many of us know the importance of scripting out our users on a regular bases in order to be able to before DR restores more efficiently. If you are responsible for performing backup and restores or disaster recovery solutions and you do not have a routine to regularly script out your users and permissions, then you should implement one soon.
A common problem when DBA’s have to restore production user databases to another server is that none of their users exist on the new server. Depending on the level of disaster, you may not have access to a working master database on the primary server. You would then find yourself having to manually create the users or add AD users to the new server, or have to restore the master database from the old server and script the users out.
You don’t want to be in that type of situation. Microsoft provides us a nice sp_help_revlogin script that will easily script out your users. The issue with this script is that it does not include the users permissions. Luckily this is not a brand new issue and my friend Kendal Van Dyke (blog)has published a great article with the scripts on how to get your users permissions and roles. Check that out here.
A common problem that you may face if you are testing out the user create scripts is that if you try to run the script to create users on a server that does not support the complexity requirements or has a more strict password requirement, the script may fail. You may get an error message similar to “Invalid value given for parameter PASSWORD”.
A good practice would be to implement a process to run both of the scripts above to output the values to a file that you backup each night with your databases. There are plenty of documentation available on the web to help you get the output into text files. I use sqlcmd and bcp to get the output into the format that I need. I have seen others that just create a two step job and use the “Output file:” option to save the results to a file. I got more complex so that the files I create are ready to execute as part of my DR policy without requiring any modification to the results.
I hope you found this helpful and that you have a solid recovery plan that you rehearse on a regular basis. Something you should try is to get someone completely unfamiliar with SQL Server and have them execute your recovery plan. If they can not follow it completely and recover your system, update your recovery plan. Who is to say that when disaster strikes, that you are available with your institutional knowledge to bring the system up. Who is to say that your priority will be your company if disaster strikes. With the recent events in New York and New Jersey with hurricane Sandy, where would your priority reside? Trying to fail over your company to another datacenter or finding food and shelter for your wife/husband and kids? If you spent the time and have a fool proof recovery plan that has been executed and tested multiple times, you could be rest assured that the support staff in that remote datacenter could execute your recovery plan to bring your company back online. Do yourself the favor, plan for the failure and test the plan.
