In a previous post, Disaster Recovery and SQL Server, Part I,

I pointed to the Microsoft Knowledge Base (KB) article which contains

stored procedures that generate scripts to recreate logins and

passwords. This sort of thing is extremely useful for disaster

recovery, especially when we have to consolidate databases and logins

onto fewer servers than we have in our normal operating locations. Microsoft has published a new

KB article for doing the same thing in SQL Server 2005, with an update

to sp_help_revlogin. You can find that KB article here:

How to transfer the logins and the passwords between instances of SQL Server 2005

If you're seeing the article and it says last updated May 24, 2006 (Revision 2.0), there is a small bug in the script for sp_help_revlogin.

A comma is left out between the SID and the DEFAULT_DATABASE parameter

when dealing with a SQL Server login. The original text:

SET @tmpstr = 'CREATE

LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + '

HASHED, SID = ' + @SID_string + ' DEFAULT_DATABASE = [' + @defaultdb +

']'

and what it should be (comma highlighted in red):

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

The SQL Server 2005 version of sp_help_revlogin does handle

checking the local security policy for passwords, but it again leaves

out the default language. Therefore, if you need to specify languages

other than the default for your SQL Server setup, make sure to extract

these with another script, something like:

SELECT 'ALTER LOGIN [' + name + '] WITH DEFAULT_LANGUAGE = ['

  + default_language_name + ']'

FROM sys.server_principals

WHERE name <> 'sa'

  AND type IN ('G', 'S', 'U') 

  AND default_language_name IS NOT NULL

This script only pulls logins where the default language is set. The

type specifies only Windows groups (type 'G'), Windows users (type

'U'), and SQL Server users (type 'S'). If you try to ALTER the LOGIN on

another server principal, such as the certificate mapped logins (type

'C'), you'll get an error, hence the reason for the type specification

in the predicate.