One of the situations any SQL Server DBA may face is that there will be less hardware available in a disaster recovery situation. After all, each additional server purchased for a secondary operating location or placed on a disaster recovery contract is extra cost for the organization. Therefore, a smart organization tries to reduce this cost like it would any other expense. If an organization can get away with 1 SQL Server in the first few days of a DR situation when it would normally operate with 5, the organization will do so and plan on increasing capacity after critical systems are brought on-line. This can put the DBA in a quandry as he or she consolidates the contents of these 5 SQL Servers onto 1.
There are a lot of challenges to this, but one of the biggest is ensuring all needed logins are created. Windows logins aren't a big deal. Execute the appropriate sp_grantlogin and things go swimmingly well. Restore the user databases and the SIDs match up. Since the passwords are on the Windows-side, there isn't anything else for the DBA to worry about. But SQL Server-based logins are a totally different story. Some third party applications have a set password that cannot be changed. Sometimes this password is created by the application when it is installed, meaning no one knows what the password actually is. A couple of the applications I deal with have this problem. Therefore, a DBA can't just execute an sp_addlogin command with any old password and get things to work with the application. The application doesn't allow for the password to be changed. That means the correct password must be set. But if you don't know what it is to begin with, does that mean you have to crack the password? No, it doesn't. That's time-consuming and ultimately unnecessary. Another issue is matching up the SID between the login and the user. Yes, running sp_change_users_login can get the SIDs to match up again, but that's unnecessary, too. In SQL Server 7.0 and SQL Server 2000, both the password and the SID is stored in the syslogins system table. If there was a way to extract this information and build a script using it, we'd be all set. After all, sp_addlogin allows for us to set the SID and the password (even in an encrypted state). And Microsoft has provided a solution in the following Knowledge Base article:
How to transfer logins and passwords between instances of SQL Server (246133)
The KB article has a script which creates two stored procedures: sp_hexadecimal and sp_help_revlogin. These get added to your master database. When you execute sp_help_revlogin (which uses sp_hexadecimal), it'll create a script that recreates all the logins on the SQL Server in question. An example output is the following:
/* sp_help_revlogin script
** Generated May 20 2006 9:40PM on TESTSQL */
DECLARE @pwd sysname
-- Login: BUILTIN\Administrators
EXEC master..sp_grantlogin 'BUILTIN\Administrators'
-- Login: builtin\users
EXEC master..sp_grantlogin 'builtin\users'
-- Login: TestUser
SET @pwd = CONVERT (varbinary(256), 0x0100B7745C01DBEE94BE4032E5A8382A75A6C334DBE408884646976A9B723EC1B6B8D43D596BC2DE97DEA7C4005F)
EXEC master..sp_addlogin 'TestUser', @pwd, @sid = 0x813E48ED68B83C438FA9DF3D1CBFD70D, @encryptopt = 'skip_encryption'
Looking at the output, you've probably noticed that it doesn't set the default database and language. Some applications aren't smart enough to change the database. In a disaster recovery situation this is not something you want to be troubleshooting. That means you want to go ahead and set the database and language when you create the login. That is easily remedied with a script like the following:
SELECT 'EXEC sp_defaultdb [' + name + '], [' + dbname + ']; ' +
'EXEC sp_defaultlanguage [' + name + '], [' + language + '];'
WHERE name <> 'sa'
All of the generated login scripts can be combined into a set of "master" login scripts to run in a DR situation. Keep in mind that these scripts need to be protected because even though the SQL Server login passwords are encrypted, there are tools that can crack them. Therefore, treat them with the appropriate care.
I'll be blogging more about disaster recovery and SQL Server in the coming weeks. Let me take this opportunity, though, to plug an old friend, Chris Kempster. Chris has written an excellent eBook on SQL Server disaster recovery and it is provided free by Quest Software: SQL Server eBook: A Practical Guide To Backup, Recovery, and Troubleshooting. You will be required to create a login on the Quest site. You can find a review of the eBook here on sql-server-performance.com.